<!DOCTYPE HTML>
<html lang="zh-CN">


<head>
    <meta charset="utf-8">
    <meta name="keywords" content="explain, Gtwff">
    <meta name="description" content="">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
    <meta name="renderer" content="webkit|ie-stand|ie-comp">
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="format-detection" content="telephone=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <meta name="referrer" content="no-referrer-when-downgrade">
    <!-- Global site tag (gtag.js) - Google Analytics -->

<script async src="https://www.googletagmanager.com/gtag/js?id="></script>
<script>
    window.dataLayer = window.dataLayer || [];
    function gtag() {
        dataLayer.push(arguments);
    }

    gtag('js', new Date());
    gtag('config', '');
</script>


    <title>explain | Gtwff</title>
    <link rel="icon" type="image/png" href="/favicon.png">

    <link rel="stylesheet" type="text/css" href="/libs/awesome/css/all.css">
    <link rel="stylesheet" type="text/css" href="/libs/materialize/materialize.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/aos/aos.css">
    <link rel="stylesheet" type="text/css" href="/libs/animate/animate.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/lightGallery/css/lightgallery.min.css">
    <link rel="stylesheet" type="text/css" href="/css/matery.css">
    <link rel="stylesheet" type="text/css" href="/css/my.css">

    <script src="/libs/jquery/jquery.min.js"></script>

<meta name="generator" content="Hexo 5.4.0"></head>



   <style>
    body{
       background-image: url(https://cdn.jsdelivr.net/gh/Tokisaki-Galaxy/res/site/medias/background.jpg);
       background-repeat:no-repeat;
       background-size:cover;
       background-attachment:fixed;
    }
</style>



<body>
    <header class="navbar-fixed">
    <nav id="headNav" class="bg-color nav-transparent">
        <div id="navContainer" class="nav-wrapper container">
            <div class="brand-logo">
                <a href="/" class="waves-effect waves-light">
                    
                    <img src="/medias/logo.png" class="logo-img" alt="LOGO">
                    
                    <span class="logo-span">Gtwff</span>
                </a>
            </div>
            

<a href="#" data-target="mobile-nav" class="sidenav-trigger button-collapse"><i class="fas fa-bars"></i></a>
<ul class="right nav-menu">
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/" class="waves-effect waves-light">
      
      <i class="fas fa-home" style="zoom: 0.6;"></i>
      
      <span>首页</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/tags" class="waves-effect waves-light">
      
      <i class="fas fa-tags" style="zoom: 0.6;"></i>
      
      <span>标签</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/categories" class="waves-effect waves-light">
      
      <i class="fas fa-bookmark" style="zoom: 0.6;"></i>
      
      <span>分类</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/archives" class="waves-effect waves-light">
      
      <i class="fas fa-archive" style="zoom: 0.6;"></i>
      
      <span>归档</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/about" class="waves-effect waves-light">
      
      <i class="fas fa-user-circle" style="zoom: 0.6;"></i>
      
      <span>关于</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/contact" class="waves-effect waves-light">
      
      <i class="fas fa-comments" style="zoom: 0.6;"></i>
      
      <span>留言板</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/friends" class="waves-effect waves-light">
      
      <i class="fas fa-address-book" style="zoom: 0.6;"></i>
      
      <span>友情链接</span>
    </a>
    
  </li>
  
  <li>
    <a href="#searchModal" class="modal-trigger waves-effect waves-light">
      <i id="searchIcon" class="fas fa-search" title="搜索" style="zoom: 0.85;"></i>
    </a>
  </li>
</ul>


<div id="mobile-nav" class="side-nav sidenav">

    <div class="mobile-head bg-color">
        
        <img src="/medias/logo.png" class="logo-img circle responsive-img">
        
        <div class="logo-name">Gtwff</div>
        <div class="logo-desc">
            
            Never really desperate, only the lost of the soul.
            
        </div>
    </div>

    <ul class="menu-list mobile-menu-list">
        
        <li class="m-nav-item">
	  
		<a href="/" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-home"></i>
			
			首页
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/tags" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-tags"></i>
			
			标签
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/categories" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-bookmark"></i>
			
			分类
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/archives" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-archive"></i>
			
			归档
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/about" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-user-circle"></i>
			
			关于
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/contact" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-comments"></i>
			
			留言板
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/friends" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-address-book"></i>
			
			友情链接
		</a>
          
        </li>
        
        
        <li><div class="divider"></div></li>
        <li>
            <a href="https://gitee.com/kuangty/kuangty" class="waves-effect waves-light" target="_blank">
                <i class="fab fa-github-square fa-fw"></i>Fork Me
            </a>
        </li>
        
    </ul>
</div>


        </div>

        
            <style>
    .nav-transparent .github-corner {
        display: none !important;
    }

    .github-corner {
        position: absolute;
        z-index: 10;
        top: 0;
        right: 0;
        border: 0;
        transform: scale(1.1);
    }

    .github-corner svg {
        color: #0f9d58;
        fill: #fff;
        height: 64px;
        width: 64px;
    }

    .github-corner:hover .octo-arm {
        animation: a 0.56s ease-in-out;
    }

    .github-corner .octo-arm {
        animation: none;
    }

    @keyframes a {
        0%,
        to {
            transform: rotate(0);
        }
        20%,
        60% {
            transform: rotate(-25deg);
        }
        40%,
        80% {
            transform: rotate(10deg);
        }
    }
</style>

<a href="https://gitee.com/kuangty/kuangty" class="github-corner tooltipped hide-on-med-and-down" target="_blank"
   data-tooltip="Fork Me" data-position="left" data-delay="50">
    <svg viewBox="0 0 250 250" aria-hidden="true">
        <path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path>
        <path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2"
              fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path>
        <path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z"
              fill="currentColor" class="octo-body"></path>
    </svg>
</a>
        
    </nav>

</header>

    



<div class="bg-cover pd-header post-cover" style="background-image: url('/medias/featureimages/1.jpg')">
    <div class="container" style="right: 0px;left: 0px;">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="brand">
                    <h1 class="description center-align post-title">explain</h1>
                </div>
            </div>
        </div>
    </div>
</div>




<main class="post-container content">

    
    <link rel="stylesheet" href="/libs/tocbot/tocbot.css">
<style>
    #articleContent h1::before,
    #articleContent h2::before,
    #articleContent h3::before,
    #articleContent h4::before,
    #articleContent h5::before,
    #articleContent h6::before {
        display: block;
        content: " ";
        height: 100px;
        margin-top: -100px;
        visibility: hidden;
    }

    #articleContent :focus {
        outline: none;
    }

    .toc-fixed {
        position: fixed;
        top: 64px;
    }

    .toc-widget {
        width: 345px;
        padding-left: 20px;
    }

    .toc-widget .toc-title {
        padding: 35px 0 15px 17px;
        font-size: 1.5rem;
        font-weight: bold;
        line-height: 1.5rem;
    }

    .toc-widget ol {
        padding: 0;
        list-style: none;
    }

    #toc-content {
        padding-bottom: 30px;
        overflow: auto;
    }

    #toc-content ol {
        padding-left: 10px;
    }

    #toc-content ol li {
        padding-left: 10px;
    }

    #toc-content .toc-link:hover {
        color: #42b983;
        font-weight: 700;
        text-decoration: underline;
    }

    #toc-content .toc-link::before {
        background-color: transparent;
        max-height: 25px;

        position: absolute;
        right: 23.5vw;
        display: block;
    }

    #toc-content .is-active-link {
        color: #42b983;
    }

    #floating-toc-btn {
        position: fixed;
        right: 15px;
        bottom: 76px;
        padding-top: 15px;
        margin-bottom: 0;
        z-index: 998;
    }

    #floating-toc-btn .btn-floating {
        width: 48px;
        height: 48px;
    }

    #floating-toc-btn .btn-floating i {
        line-height: 48px;
        font-size: 1.4rem;
    }
</style>
<div class="row">
    <div id="main-content" class="col s12 m12 l9">
        <!-- 文章内容详情 -->
<div id="artDetail">
    <div class="card">
        <div class="card-content article-info">
            <div class="row tag-cate">
                <div class="col s7">
                    
                    <div class="article-tag">
                        
                            <a href="/tags/explain/">
                                <span class="chip bg-color">explain</span>
                            </a>
                        
                    </div>
                    
                </div>
                <div class="col s5 right-align">
                    
                    <div class="post-cate">
                        <i class="fas fa-bookmark fa-fw icon-category"></i>
                        
                            <a href="/categories/MySQL/" class="post-category">
                                MySQL
                            </a>
                        
                    </div>
                    
                </div>
            </div>

            <div class="post-info">
                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-minus fa-fw"></i>发布日期:&nbsp;&nbsp;
                    2021-10-31
                </div>
                

                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-check fa-fw"></i>更新日期:&nbsp;&nbsp;
                    2022-03-27
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-file-word fa-fw"></i>文章字数:&nbsp;&nbsp;
                    6.7k
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-clock fa-fw"></i>阅读时长:&nbsp;&nbsp;
                    30 分
                </div>
                

                
                    <div id="busuanzi_container_page_pv" class="info-break-policy">
                        <i class="far fa-eye fa-fw"></i>阅读次数:&nbsp;&nbsp;
                        <span id="busuanzi_value_page_pv"></span>
                    </div>
				
            </div>
        </div>
        <hr class="clearfix">

        
        <!-- 是否加载使用自带的 prismjs. -->
        <link rel="stylesheet" href="/libs/prism/prism.css">
        

        
        <!-- 代码块折行 -->
        <style type="text/css">
            code[class*="language-"], pre[class*="language-"] { white-space: pre-wrap !important; }
        </style>
        

        <div class="card-content article-card-content">
            <div id="articleContent">
                <h1 id="explain"><a href="#explain" class="headerlink" title="explain"></a>explain</h1><h2 id="1-数据库服务器的优化步骤"><a href="#1-数据库服务器的优化步骤" class="headerlink" title="1. 数据库服务器的优化步骤"></a>1. 数据库服务器的优化步骤</h2><p>当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。<br>整个流程划分成了观察（Show status） 和行动（Action） 两个部分。字母 S 的部分代表观察（会使<br>用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。</p>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111148427.png" alt="image-20220327111148427" style="zoom:80%;" />

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111155008.png" alt="image-20220327111155008" style="zoom:80%;" />

<h2 id="2-查看系统性能参数"><a href="#2-查看系统性能参数" class="headerlink" title="2. 查看系统性能参数"></a>2. 查看系统性能参数</h2><p>在MySQL中，可以使用SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。<br>SHOW STATUS语句语法如下：<br>一些常用的性能参数如下： •　Connections：连接MySQL服务器的次数。 •　Uptime：MySQL服务器的上<br>线时间。 •　Slow_queries：慢查询的次数。 •　Innodb_rows_read：Select查询返回的行数 •　<br>Innodb_rows_inserted：执行INSERT操作插入的行数 •　Innodb_rows_updated：执行UPDATE操作更新的<br>行数 •　Innodb_rows_deleted：执行DELETE操作删除的行数 •　Com_select：查询操作的次数。 •　<br>Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。 •　Com_update：更新操作<br>的次数。 •　Com_delete：删除操作的次数。</p>
<h2 id="3-统计SQL的查询成本：last-query-cost"><a href="#3-统计SQL的查询成本：last-query-cost" class="headerlink" title="3. 统计SQL的查询成本：last_query_cost"></a>3. 统计SQL的查询成本：last_query_cost</h2><p>我们依然使用第8章的 student_info 表为例：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>student_info<span class="token punctuation">`</span> <span class="token punctuation">(</span>
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>student_id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token punctuation">,</span>
<span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>course_id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token punctuation">,</span>
<span class="token punctuation">`</span>class_id<span class="token punctuation">`</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>create_time<span class="token punctuation">`</span> <span class="token keyword">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> student_id<span class="token punctuation">,</span> class_id<span class="token punctuation">,</span> NAME<span class="token punctuation">,</span> create_time <span class="token keyword">FROM</span> student_info
<span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">900001</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>运行结果（1 条记录，运行时间为 0.042s ）<br>然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">SHOW</span> <span class="token keyword">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'last_query_cost'</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----------------+----------+</span>
<span class="token operator">|</span> Variable_name <span class="token operator">|</span> <span class="token keyword">Value</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------+----------+</span>
<span class="token operator">|</span> Last_query_cost <span class="token operator">|</span> <span class="token number">1.000000</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------+----------+</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> student_id<span class="token punctuation">,</span> class_id<span class="token punctuation">,</span> NAME<span class="token punctuation">,</span> create_time <span class="token keyword">FROM</span> student_info
<span class="token keyword">WHERE</span> id <span class="token operator">BETWEEN</span> <span class="token number">900001</span> <span class="token operator">AND</span> <span class="token number">900100</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>运行结果（100 条记录，运行时间为 0.046s ）：<br>然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">SHOW</span> <span class="token keyword">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'last_query_cost'</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">-----------------+-----------+</span>
<span class="token operator">|</span> Variable_name <span class="token operator">|</span> <span class="token keyword">Value</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------+-----------+</span>
<span class="token operator">|</span> Last_query_cost <span class="token operator">|</span> <span class="token number">21.134453</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">-----------------+-----------+</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间<br>基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页<br>数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。<br>使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。</p>
<h2 id="4-定位执行慢的-SQL：慢查询日志"><a href="#4-定位执行慢的-SQL：慢查询日志" class="headerlink" title="4. 定位执行慢的 SQL：慢查询日志"></a>4. 定位执行慢的 SQL：慢查询日志</h2><h3 id="4-1-开启慢查询日志参数"><a href="#4-1-开启慢查询日志参数" class="headerlink" title="4.1 开启慢查询日志参数"></a>4.1 开启慢查询日志参数</h3><h4 id="1-开启slow-query-log"><a href="#1-开启slow-query-log" class="headerlink" title="1. 开启slow_query_log"></a>1. 开启slow_query_log</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql <span class="token operator">></span> <span class="token keyword">set</span> <span class="token keyword">global</span> slow_query_log<span class="token operator">=</span><span class="token string">'ON'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：</p>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111206267.png" alt="image-20220327111206267" style="zoom:80%;" />

<p>你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件<br>中。</p>
<h4 id="2-修改long-query-time阈值"><a href="#2-修改long-query-time阈值" class="headerlink" title="2. 修改long_query_time阈值"></a>2. 修改long_query_time阈值</h4><p>接下来我们来看下慢查询的时间阈值设置，使用如下命令：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql <span class="token operator">></span> <span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'%long_query_time%'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111308574.png" alt="image-20220327111308574" style="zoom:80%;" />

<p>这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并</span>
执行下述语句
mysql <span class="token operator">></span> <span class="token keyword">set</span> <span class="token keyword">global</span> long_query_time <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
mysql<span class="token operator">></span> <span class="token keyword">show</span> <span class="token keyword">global</span> variables <span class="token operator">like</span> <span class="token string">'%long_query_time%'</span><span class="token punctuation">;</span>
mysql<span class="token operator">></span> <span class="token keyword">set</span> long_query_time<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
mysql<span class="token operator">></span> <span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'%long_query_time%'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111317100.png" alt="image-20220327111317100" style="zoom:80%;" />

<h3 id="4-2-查看慢查询数目"><a href="#4-2-查看慢查询数目" class="headerlink" title="4.2 查看慢查询数目"></a>4.2 查看慢查询数目</h3><p>查询当前系统中有多少条慢查询记录</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SHOW</span> <span class="token keyword">GLOBAL</span> <span class="token keyword">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'%Slow_queries%'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<h3 id="4-3-案例演示"><a href="#4-3-案例演示" class="headerlink" title="4.3 案例演示"></a>4.3 案例演示</h3><h4 id="步骤1-建表"><a href="#步骤1-建表" class="headerlink" title="步骤1. 建表"></a>步骤1. 建表</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>student<span class="token punctuation">`</span> <span class="token punctuation">(</span>
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>stuno<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token punctuation">,</span>
<span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>classId<span class="token punctuation">`</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="步骤2：设置参数-log-bin-trust-function-creators"><a href="#步骤2：设置参数-log-bin-trust-function-creators" class="headerlink" title="步骤2：设置参数 log_bin_trust_function_creators"></a>步骤2：设置参数 log_bin_trust_function_creators</h4><p>创建函数，假如报错：</p>
<pre class="line-numbers language-bash" data-language="bash"><code class="language-bash">This <span class="token keyword">function</span> has none of DETERMINISTIC<span class="token punctuation">..</span><span class="token punctuation">..</span><span class="token punctuation">..</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<ul>
<li>命令开启：允许创建函数设置：</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">set</span> <span class="token keyword">global</span> log_bin_trust_function_creators<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span> <span class="token comment"># 不加global只是当前窗口有效。</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<h4 id="步骤3：创建函数"><a href="#步骤3：创建函数" class="headerlink" title="步骤3：创建函数"></a>步骤3：创建函数</h4><p>随机产生字符串：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> rand_string<span class="token punctuation">(</span>n <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token comment">#该函数会返回一个字符串</span>
<span class="token keyword">BEGIN</span>
<span class="token keyword">DECLARE</span> chars_str <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span>
<span class="token string">'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'</span><span class="token punctuation">;</span>
<span class="token keyword">DECLARE</span> return_str <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">WHILE</span> i <span class="token operator">&lt;</span> n <span class="token keyword">DO</span>
<span class="token keyword">SET</span> return_str <span class="token operator">=</span>CONCAT<span class="token punctuation">(</span>return_str<span class="token punctuation">,</span>SUBSTRING<span class="token punctuation">(</span>chars_str<span class="token punctuation">,</span>FLOOR<span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">+</span>RAND<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">*</span><span class="token number">52</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
<span class="token keyword">RETURN</span> return_str<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
<span class="token comment">#测试</span>
<span class="token keyword">SELECT</span> rand_string<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>产生随机数值：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> rand_num <span class="token punctuation">(</span>from_num <span class="token keyword">INT</span> <span class="token punctuation">,</span>to_num <span class="token keyword">INT</span><span class="token punctuation">)</span> <span class="token keyword">RETURNS</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
<span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> i <span class="token operator">=</span> FLOOR<span class="token punctuation">(</span>from_num <span class="token operator">+</span>RAND<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">*</span><span class="token punctuation">(</span>to_num <span class="token operator">-</span> from_num<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">;</span>
<span class="token keyword">RETURN</span> i<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
<span class="token comment">#测试：</span>
<span class="token keyword">SELECT</span> rand_num<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="步骤4：创建存储过程"><a href="#步骤4：创建存储过程" class="headerlink" title="步骤4：创建存储过程"></a>步骤4：创建存储过程</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> insert_stu1<span class="token punctuation">(</span> <span class="token keyword">START</span> <span class="token keyword">INT</span> <span class="token punctuation">,</span> max_num <span class="token keyword">INT</span> <span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
<span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> autocommit <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span> <span class="token comment">#设置手动提交事务</span>
<span class="token keyword">REPEAT</span> <span class="token comment">#循环</span>
<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span> <span class="token comment">#赋值</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> student <span class="token punctuation">(</span>stuno<span class="token punctuation">,</span> NAME <span class="token punctuation">,</span>age <span class="token punctuation">,</span>classId <span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
<span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token keyword">START</span><span class="token operator">+</span>i<span class="token punctuation">)</span><span class="token punctuation">,</span>rand_string<span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span>rand_num<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>rand_num<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
UNTIL i <span class="token operator">=</span> max_num
<span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span> <span class="token comment">#提交事务</span>
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="步骤5：调用存储过程"><a href="#步骤5：调用存储过程" class="headerlink" title="步骤5：调用存储过程"></a>步骤5：调用存储过程</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#调用刚刚写好的函数, 4000000条记录,从100001号开始</span>
<span class="token keyword">CALL</span> insert_stu1<span class="token punctuation">(</span><span class="token number">100001</span><span class="token punctuation">,</span><span class="token number">4000000</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<h3 id="4-4-测试及分析"><a href="#4-4-测试及分析" class="headerlink" title="4.4 测试及分析"></a>4.4 测试及分析</h3><h4 id="1-测试"><a href="#1-测试" class="headerlink" title="1. 测试"></a>1. 测试</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> student <span class="token keyword">WHERE</span> stuno <span class="token operator">=</span> <span class="token number">3455655</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">---------+---------+--------+------+---------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> stuno <span class="token operator">|</span> name <span class="token operator">|</span> age <span class="token operator">|</span> classId <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------+---------+--------+------+---------+</span>
<span class="token operator">|</span> <span class="token number">3523633</span> <span class="token operator">|</span> <span class="token number">3455655</span> <span class="token operator">|</span> oQmLUr <span class="token operator">|</span> <span class="token number">19</span> <span class="token operator">|</span> <span class="token number">39</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------+---------+--------+------+---------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">2.09</span> sec<span class="token punctuation">)</span>
mysql<span class="token operator">></span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> student <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'oQmLUr'</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">---------+---------+--------+------+---------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> stuno <span class="token operator">|</span> name <span class="token operator">|</span> age <span class="token operator">|</span> classId <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------+---------+--------+------+---------+</span>
<span class="token operator">|</span> <span class="token number">1154002</span> <span class="token operator">|</span> <span class="token number">1243200</span> <span class="token operator">|</span> OQMlUR <span class="token operator">|</span> <span class="token number">266</span> <span class="token operator">|</span> <span class="token number">28</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1405708</span> <span class="token operator">|</span> <span class="token number">1437740</span> <span class="token operator">|</span> OQMlUR <span class="token operator">|</span> <span class="token number">245</span> <span class="token operator">|</span> <span class="token number">439</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">1748070</span> <span class="token operator">|</span> <span class="token number">1680092</span> <span class="token operator">|</span> OQMlUR <span class="token operator">|</span> <span class="token number">240</span> <span class="token operator">|</span> <span class="token number">414</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">2119892</span> <span class="token operator">|</span> <span class="token number">2051914</span> <span class="token operator">|</span> oQmLUr <span class="token operator">|</span> <span class="token number">17</span> <span class="token operator">|</span> <span class="token number">32</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">2893154</span> <span class="token operator">|</span> <span class="token number">2825176</span> <span class="token operator">|</span> OQMlUR <span class="token operator">|</span> <span class="token number">245</span> <span class="token operator">|</span> <span class="token number">435</span> <span class="token operator">|</span>
<span class="token operator">|</span> <span class="token number">3523633</span> <span class="token operator">|</span> <span class="token number">3455655</span> <span class="token operator">|</span> oQmLUr <span class="token operator">|</span> <span class="token number">19</span> <span class="token operator">|</span> <span class="token number">39</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">---------+---------+--------+------+---------+</span>
<span class="token number">6</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">2.39</span> sec<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>从上面的结果可以看出来，查询学生编号为“3455655”的学生信息花费时间为2.09秒。查询学生姓名为<br>“oQmLUr”的学生信息花费时间为2.39秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面<br>的小节我们分析一下原因。</p>
<h4 id="2-分析"><a href="#2-分析" class="headerlink" title="2. 分析"></a>2. 分析</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">show</span> <span class="token keyword">status</span> <span class="token operator">like</span> <span class="token string">'slow_queries'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<h3 id="4-5-慢查询日志分析工具：mysqldumpslow"><a href="#4-5-慢查询日志分析工具：mysqldumpslow" class="headerlink" title="4.5 慢查询日志分析工具：mysqldumpslow"></a>4.5 慢查询日志分析工具：mysqldumpslow</h3><p>在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具<br>mysqldumpslow 。<br>查看mysqldumpslow的帮助信息</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysqldumpslow <span class="token comment">--help</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111326520.png" alt="image-20220327111326520" style="zoom:80%;" />

<p>mysqldumpslow 命令的具体参数如下：</p>
<ul>
<li>-a: 不将数字抽象成N，字符串抽象成S</li>
<li>-s: 是表示按照何种方式排序：<ul>
<li>c: 访问次数</li>
<li>l: 锁定时间</li>
<li>r: 返回记录</li>
<li>t: 查询时间</li>
<li>al:平均锁定时间</li>
<li>ar:平均返回记录数</li>
<li>at:平均查询时间 （默认方式）</li>
<li>ac:平均查询次数</li>
</ul>
</li>
<li>-t: 即为返回前面多少条的数据；<ul>
<li>-g: 后边搭配一个正则匹配模式，大小写不敏感的；</li>
</ul>
</li>
</ul>
<p>举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysqldumpslow <span class="token operator">-</span>s t <span class="token operator">-</span>t <span class="token number">5</span> <span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">/</span>atguigu01<span class="token operator">-</span>slow<span class="token punctuation">.</span>log<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token punctuation">[</span>root<span class="token variable">@bogon</span> <span class="token operator">~</span><span class="token punctuation">]</span><span class="token comment"># mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log</span>
Reading mysql slow query log <span class="token keyword">from</span> <span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">/</span>atguigu01<span class="token operator">-</span>slow<span class="token punctuation">.</span>log
Count: <span class="token number">1</span> <span class="token keyword">Time</span><span class="token operator">=</span><span class="token number">2.39</span>s <span class="token punctuation">(</span><span class="token number">2</span>s<span class="token punctuation">)</span> <span class="token keyword">Lock</span><span class="token operator">=</span><span class="token number">0.00</span>s <span class="token punctuation">(</span><span class="token number">0</span>s<span class="token punctuation">)</span> <span class="token keyword">Rows</span><span class="token operator">=</span><span class="token number">13.0</span> <span class="token punctuation">(</span><span class="token number">13</span><span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">[</span>root<span class="token punctuation">]</span><span class="token variable">@localhost</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> student <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'S'</span>
Count: <span class="token number">1</span> <span class="token keyword">Time</span><span class="token operator">=</span><span class="token number">2.09</span>s <span class="token punctuation">(</span><span class="token number">2</span>s<span class="token punctuation">)</span> <span class="token keyword">Lock</span><span class="token operator">=</span><span class="token number">0.00</span>s <span class="token punctuation">(</span><span class="token number">0</span>s<span class="token punctuation">)</span> <span class="token keyword">Rows</span><span class="token operator">=</span><span class="token number">2.0</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">[</span>root<span class="token punctuation">]</span><span class="token variable">@localhost</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> student <span class="token keyword">WHERE</span> stuno <span class="token operator">=</span> N
Died at <span class="token operator">/</span>usr<span class="token operator">/</span>bin<span class="token operator">/</span>mysqldumpslow line <span class="token number">162</span><span class="token punctuation">,</span> <span class="token operator">&lt;></span> chunk <span class="token number">2.</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>工作常用参考：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#得到返回记录集最多的10个SQL</span>
mysqldumpslow <span class="token operator">-</span>s r <span class="token operator">-</span>t <span class="token number">10</span> <span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">/</span>atguigu<span class="token operator">-</span>slow<span class="token punctuation">.</span>log
<span class="token comment">#得到访问次数最多的10个SQL</span>
mysqldumpslow <span class="token operator">-</span>s c <span class="token operator">-</span>t <span class="token number">10</span> <span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">/</span>atguigu<span class="token operator">-</span>slow<span class="token punctuation">.</span>log
<span class="token comment">#得到按照时间排序的前10条里面含有左连接的查询语句</span>
mysqldumpslow <span class="token operator">-</span>s t <span class="token operator">-</span>t <span class="token number">10</span> <span class="token operator">-</span>g <span class="token string">"left join"</span> <span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">/</span>atguigu<span class="token operator">-</span>slow<span class="token punctuation">.</span>log
<span class="token comment">#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况</span>
mysqldumpslow <span class="token operator">-</span>s r <span class="token operator">-</span>t <span class="token number">10</span> <span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">/</span>atguigu<span class="token operator">-</span>slow<span class="token punctuation">.</span>log <span class="token operator">|</span> more<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h3 id="4-6-关闭慢查询日志"><a href="#4-6-关闭慢查询日志" class="headerlink" title="4.6 关闭慢查询日志"></a>4.6 关闭慢查询日志</h3><p>MySQL服务器停止慢查询日志功能有两种方法：</p>
<h4 id="方式1：永久性方式"><a href="#方式1：永久性方式" class="headerlink" title="方式1：永久性方式"></a>方式1：永久性方式</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token punctuation">[</span>mysqld<span class="token punctuation">]</span>
slow_query_log<span class="token operator">=</span><span class="token keyword">OFF</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>或者，把slow_query_log一项注释掉 或 删除</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token punctuation">[</span>mysqld<span class="token punctuation">]</span>
<span class="token comment">#slow_query_log =OFF</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>重启MySQL服务，执行如下语句查询慢日志功能。</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%slow%'</span><span class="token punctuation">;</span> <span class="token comment">#查询慢查询日志所在目录</span>
<span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%long_query_time%'</span><span class="token punctuation">;</span> <span class="token comment">#查询超时时长</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<h4 id="方式2：临时性方式"><a href="#方式2：临时性方式" class="headerlink" title="方式2：临时性方式"></a>方式2：临时性方式</h4><p>使用SET语句来设置。 （1）停止MySQL慢查询日志功能，具体SQL语句如下。</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> slow_query_log<span class="token operator">=</span><span class="token keyword">off</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%slow%'</span><span class="token punctuation">;</span>
<span class="token comment">#以及</span>
<span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%long_query_time%'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<h2 id="5-查看-SQL-执行成本：SHOW-PROFILE"><a href="#5-查看-SQL-执行成本：SHOW-PROFILE" class="headerlink" title="5. 查看 SQL 执行成本：SHOW PROFILE"></a>5. 查看 SQL 执行成本：SHOW PROFILE</h2><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql <span class="token operator">></span> <span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'profiling'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111335589.png" alt="image-20220327111335589" style="zoom:80%;" />

<p>通过设置 profiling=’ON’ 来开启 show profile：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql <span class="token operator">></span> <span class="token keyword">set</span> profiling <span class="token operator">=</span> <span class="token string">'ON'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111340673.png" alt="image-20220327111340673" style="zoom:80%;" />

<p>然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql <span class="token operator">></span> <span class="token keyword">show</span> profiles<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111345916.png" alt="image-20220327111345916" style="zoom:80%;" />

<p>你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql <span class="token operator">></span> <span class="token keyword">show</span> profile<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111350775.png" alt="image-20220327111350775" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">show</span> profile cpu<span class="token punctuation">,</span>block io <span class="token keyword">for</span> query <span class="token number">2</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111359996.png" alt="image-20220327111359996" style="zoom:80%;" />

<p>show profile的常用查询参数：</p>
<p>① ALL：显示所有的开销信息。 ② BLOCK IO：显示块IO开销。 ③ CONTEXT SWITCHES：上下文切换开<br>销。 ④ CPU：显示CPU开销信息。 ⑤ IPC：显示发送和接收开销信息。 ⑥ MEMORY：显示内存开销信<br>息。 ⑦ PAGE FAULTS：显示页面错误开销信息。 ⑧ SOURCE：显示和Source_function，Source_file，<br>Source_line相关的开销信息。 ⑨ SWAPS：显示交换次数开销信息。</p>
<h2 id="6-分析查询语句：EXPLAIN"><a href="#6-分析查询语句：EXPLAIN" class="headerlink" title="6. 分析查询语句：EXPLAIN"></a>6. 分析查询语句：EXPLAIN</h2><h3 id="6-1-概述"><a href="#6-1-概述" class="headerlink" title="6.1 概述"></a>6.1 概述</h3><p>官网介绍<br><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html">https://dev.mysql.com/doc/refman/5.7/en/explain-output.html</a><br><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html">https://dev.mysql.com/doc/refman/8.0/en/explain-output.html</a></p>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111408070.png" alt="image-20220327111408070" style="zoom:80%;" />

<p>版本情况</p>
<ul>
<li>MySQL 5.6.3以前只能EXPLAIN SELECT ；MYSQL 5.6.3以后就可以EXPLAIN SELECT，UPDATE，<br>DELETE</li>
<li>在5.7以前的版本中，想要显示partitions 需要使用explain partitions 命令；想要显示<br>filtered 需要使用explain extended 命令。在5.7版本后，默认explain直接显示partitions和<br>filtered中的信息。</li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111420554.png" alt="image-20220327111420554" style="zoom:80%;" />

<h3 id="6-2-基本语法"><a href="#6-2-基本语法" class="headerlink" title="6.2 基本语法"></a>6.2 基本语法</h3><p>EXPLAIN 或 DESCRIBE语句的语法形式如下：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> select_options
或者
<span class="token keyword">DESCRIBE</span> <span class="token keyword">SELECT</span> select_options<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<p>如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个EXPLAIN ，就像这样：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>EXPLAIN 语句输出的各个列的作用如下：</p>
<table>
<thead>
<tr>
<th>列名</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>id</td>
<td>在一个大的查询语句中每个SELECT关键字都对应一个唯一的id</td>
</tr>
<tr>
<td>select_type</td>
<td>SELECT关键字对应的那个查询的类型</td>
</tr>
<tr>
<td>table</td>
<td>表名</td>
</tr>
<tr>
<td>partitions</td>
<td>匹配的分区信息</td>
</tr>
<tr>
<td>type</td>
<td>针对单表的访问方法</td>
</tr>
<tr>
<td>possible</td>
<td>可能用到的索引</td>
</tr>
<tr>
<td>key</td>
<td>实际上使用的索引</td>
</tr>
<tr>
<td>key_len</td>
<td>实际使用到的索引长度</td>
</tr>
<tr>
<td>ref</td>
<td>当使用索引列等值查询时，与索引列进行等值匹配的对象信息</td>
</tr>
<tr>
<td>rows</td>
<td>预估的需要读取的记录条数</td>
</tr>
<tr>
<td>filtered</td>
<td>某个表经过搜索条件过滤后剩余记录条数的百分比</td>
</tr>
<tr>
<td>Extra</td>
<td>一些额外的信息</td>
</tr>
</tbody></table>
<h3 id="6-3-数据准备"><a href="#6-3-数据准备" class="headerlink" title="6.3 数据准备"></a>6.3 数据准备</h3><h4 id="1、建表"><a href="#1、建表" class="headerlink" title="1、建表"></a>1、建表</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> s1 <span class="token punctuation">(</span>
id <span class="token keyword">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
key1 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key2 <span class="token keyword">INT</span><span class="token punctuation">,</span>
key3 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key_part1 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key_part2 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key_part3 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
common_field <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">INDEX</span> idx_key1 <span class="token punctuation">(</span>key1<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> idx_key2 <span class="token punctuation">(</span>key2<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">INDEX</span> idx_key3 <span class="token punctuation">(</span>key3<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">INDEX</span> idx_key_part<span class="token punctuation">(</span>key_part1<span class="token punctuation">,</span> key_part2<span class="token punctuation">,</span> key_part3<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> s2 <span class="token punctuation">(</span>
id <span class="token keyword">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
key1 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key2 <span class="token keyword">INT</span><span class="token punctuation">,</span>
key3 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key_part1 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key_part2 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
key_part3 <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
common_field <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">INDEX</span> idx_key1 <span class="token punctuation">(</span>key1<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> idx_key2 <span class="token punctuation">(</span>key2<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">INDEX</span> idx_key3 <span class="token punctuation">(</span>key3<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">INDEX</span> idx_key_part<span class="token punctuation">(</span>key_part1<span class="token punctuation">,</span> key_part2<span class="token punctuation">,</span> key_part3<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="2、设置参数-log-bin-trust-function-creators"><a href="#2、设置参数-log-bin-trust-function-creators" class="headerlink" title="2、设置参数 log_bin_trust_function_creators"></a>2、设置参数 log_bin_trust_function_creators</h4><p>创建函数，假如报错，需开启如下命令：允许创建函数设置：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">set</span> <span class="token keyword">global</span> log_bin_trust_function_creators<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span> <span class="token comment"># 不加global只是当前窗口有效。</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<h4 id="3、创建函数"><a href="#3、创建函数" class="headerlink" title="3、创建函数"></a>3、创建函数</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> rand_string1<span class="token punctuation">(</span>n <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token comment">#该函数会返回一个字符串</span>
<span class="token keyword">BEGIN</span>
<span class="token keyword">DECLARE</span> chars_str <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span>
<span class="token string">'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'</span><span class="token punctuation">;</span>
<span class="token keyword">DECLARE</span> return_str <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">WHILE</span> i <span class="token operator">&lt;</span> n <span class="token keyword">DO</span>
<span class="token keyword">SET</span> return_str <span class="token operator">=</span>CONCAT<span class="token punctuation">(</span>return_str<span class="token punctuation">,</span>SUBSTRING<span class="token punctuation">(</span>chars_str<span class="token punctuation">,</span>FLOOR<span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">+</span>RAND<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">*</span><span class="token number">52</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
<span class="token keyword">RETURN</span> return_str<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="4、创建存储过程"><a href="#4、创建存储过程" class="headerlink" title="4、创建存储过程"></a>4、创建存储过程</h4><p>创建往s1表中插入数据的存储过程：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> insert_s1 <span class="token punctuation">(</span><span class="token operator">IN</span> min_num <span class="token keyword">INT</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token operator">IN</span> max_num <span class="token keyword">INT</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
<span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> autocommit <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">REPEAT</span>
<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> s1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span>
<span class="token punctuation">(</span>min_num <span class="token operator">+</span> i<span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span>min_num <span class="token operator">+</span> <span class="token number">30</span> <span class="token operator">*</span> i <span class="token operator">+</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
UNTIL i <span class="token operator">=</span> max_num
<span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>创建往s2表中插入数据的存储过程：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> insert_s2 <span class="token punctuation">(</span><span class="token operator">IN</span> min_num <span class="token keyword">INT</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token operator">IN</span> max_num <span class="token keyword">INT</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
<span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> autocommit <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">REPEAT</span>
<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> s2 <span class="token keyword">VALUES</span><span class="token punctuation">(</span>
<span class="token punctuation">(</span>min_num <span class="token operator">+</span> i<span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span>min_num <span class="token operator">+</span> <span class="token number">30</span> <span class="token operator">*</span> i <span class="token operator">+</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
rand_string1<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
UNTIL i <span class="token operator">=</span> max_num
<span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="5、调用存储过程"><a href="#5、调用存储过程" class="headerlink" title="5、调用存储过程"></a>5、调用存储过程</h4><p>s1表数据的添加：加入1万条记录：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CALL</span> insert_s1<span class="token punctuation">(</span><span class="token number">10001</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>s2表数据的添加：加入1万条记录：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CALL</span> insert_s2<span class="token punctuation">(</span><span class="token number">10001</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<h3 id="6-4-EXPLAIN各列作用"><a href="#6-4-EXPLAIN各列作用" class="headerlink" title="6.4 EXPLAIN各列作用"></a>6.4 EXPLAIN各列作用</h3><p>为了让大家有比较好的体验，我们调整了下EXPLAIN 输出列的顺序。</p>
<h4 id="1-table"><a href="#1-table" class="headerlink" title="1. table"></a>1. table</h4><p>不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所<br>以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该<br>表的表名（有时不是真实的表名字，可能是简称）。</p>
<h4 id="2-id"><a href="#2-id" class="headerlink" title="2. id"></a>2. id</h4><p>我们写的查询语句一般都以SELECT 关键字开头，比较简单的查询语句里只有一个SELECT 关键字，比<br>如下边这个查询语句：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>稍微复杂一点的连接查询中也只有一个SELECT 关键字，比如：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2
<span class="token keyword">ON</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key1
<span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>common_field <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111454791.png" alt="image-20220327111454791" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111500369.png" alt="image-20220327111500369" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s2<span class="token punctuation">)</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111505795.png" alt="image-20220327111505795" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key2 <span class="token keyword">FROM</span> s2 <span class="token keyword">WHERE</span> common_field
<span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111512384.png" alt="image-20220327111512384" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s2<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111518876.png" alt="image-20220327111518876" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">UNION</span> <span class="token keyword">ALL</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s2<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111524507.png" alt="image-20220327111524507" style="zoom:80%;" />

<p>小结:</p>
<ul>
<li>id如果相同，可以认为是一组，从上往下顺序执行</li>
<li>在所有组中，id值越大，优先级越高，越先执行</li>
<li>关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好</li>
</ul>
<h4 id="3-select-type"><a href="#3-select-type" class="headerlink" title="3. select_type"></a>3. select_type</h4><img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111550068.png" alt="image-20220327111550068" style="zoom:80%;" />

<p>具体分析如下：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111655620.png" alt="image-20220327111655620" style="zoom:80%;" />

<p>当然，连接查询也算是SIMPLE 类型，比如：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111701242.png" alt="image-20220327111701242" style="zoom:80%;" />

<ul>
<li>PRIMARY</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s2<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111706606.png" alt="image-20220327111706606" style="zoom:80%;" />

<ul>
<li><p>UNION</p>
</li>
<li><p>UNION RESULT</p>
</li>
<li><p>SUBQUERY</p>
</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s2<span class="token punctuation">)</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111715042.png" alt="image-20220327111715042" style="zoom:80%;" />

<ul>
<li>DEPENDENT SUBQUERY</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s2 <span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>key2 <span class="token operator">=</span>
s2<span class="token punctuation">.</span>key2<span class="token punctuation">)</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111721140.png" alt="image-20220327111721140" style="zoom:80%;" />

<ul>
<li>DEPENDENT UNION</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s2 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span>
<span class="token string">'a'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'b'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111730383.png" alt="image-20220327111730383" style="zoom:80%;" />

<ul>
<li>DERIVED</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key1<span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">as</span> c <span class="token keyword">FROM</span> s1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> key1<span class="token punctuation">)</span> <span class="token keyword">AS</span>
derived_s1 <span class="token keyword">where</span> c <span class="token operator">></span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111737595.png" alt="image-20220327111737595" style="zoom:80%;" />

<ul>
<li>MATERIALIZED</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s2<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111743555.png" alt="image-20220327111743555" style="zoom:80%;" />

<ul>
<li>UNCACHEABLE SUBQUERY<br>不常用，就不多说了。</li>
<li>UNCACHEABLE UNION<br>不常用，就不多说了。</li>
</ul>
<h4 id="4-partitions-可略"><a href="#4-partitions-可略" class="headerlink" title="4. partitions (可略)"></a>4. partitions (可略)</h4><p>如果想详细了解，可以如下方式测试。创建分区表：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">-- 创建分区表，</span>
<span class="token comment">-- 按照id分区，id&lt;100 p0分区，其他p1分区</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> user_partitions <span class="token punctuation">(</span>id <span class="token keyword">INT</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span>
NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> RANGE<span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">(</span>
<span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> less than<span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> less than MAXVALUE
<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111750751.png" alt="image-20220327111750751" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DESC</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> user_partitions <span class="token keyword">WHERE</span> id<span class="token operator">></span><span class="token number">200</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>查询id大于200（200&gt;100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则</p>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111755888.png" alt="image-20220327111755888" style="zoom:80%;" />

<h4 id="5-type-☆"><a href="#5-type-☆" class="headerlink" title="5. type ☆"></a>5. type ☆</h4><p>完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，<br>index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。<br>我们详细解释一下：</p>
<ul>
<li>system</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t<span class="token punctuation">(</span>i <span class="token keyword">int</span><span class="token punctuation">)</span> <span class="token keyword">Engine</span><span class="token operator">=</span>MyISAM<span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.05</span> sec<span class="token punctuation">)</span>
mysql<span class="token operator">></span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">row</span> affected <span class="token punctuation">(</span><span class="token number">0.01</span> sec<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>

<p>然后我们看一下查询这个表的执行计划:</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111840503.png" alt="image-20220327111840503" style="zoom:80%;" />

<ul>
<li>const</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">10005</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111846792.png" alt="image-20220327111846792" style="zoom:80%;" />

<ul>
<li>eq_ref</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>id <span class="token operator">=</span> s2<span class="token punctuation">.</span>id<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111852266.png" alt="image-20220327111852266" style="zoom:80%;" />

<p>从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问<br>方法是eq_ref ，表明在访问s1表的时候可以通过主键的等值匹配来进行访问。</p>
<ul>
<li>ref</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111900040.png" alt="image-20220327111900040" style="zoom:80%;" />

<ul>
<li>fulltext<br>全文索引</li>
<li>ref_or_null</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token operator">OR</span> key1 <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111906243.png" alt="image-20220327111906243" style="zoom:80%;" />

<ul>
<li>index_merge</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111911806.png" alt="image-20220327111911806" style="zoom:80%;" />

<p>从执行计划的type 列的值是index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行<br>对s1 表的查询。</p>
<ul>
<li>unique_subquery</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key2 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> id <span class="token keyword">FROM</span> s2 <span class="token keyword">where</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span>
s2<span class="token punctuation">.</span>key1<span class="token punctuation">)</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111917590.png" alt="image-20220327111917590" style="zoom:80%;" />

<ul>
<li>index_subquery</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> common_field <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> key3 <span class="token keyword">FROM</span> s2 <span class="token keyword">where</span>
s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key1<span class="token punctuation">)</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111940443.png" alt="image-20220327111940443" style="zoom:80%;" />

<ul>
<li>range</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111945499.png" alt="image-20220327111945499" style="zoom:80%;" />

<p>或者：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">></span> <span class="token string">'a'</span> <span class="token operator">AND</span> key1 <span class="token operator">&lt;</span> <span class="token string">'b'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111951522.png" alt="image-20220327111951522" style="zoom:80%;" />

<ul>
<li>index</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> key_part2 <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key_part3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327111958431.png" alt="image-20220327111958431" style="zoom:80%;" />

<ul>
<li>ALL</li>
</ul>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112003379.png" alt="image-20220327112003379" style="zoom:80%;" />

<p>小结：<br>结果值从最好到最坏依次是： system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null &gt; index_merge &gt;<br>unique_subquery &gt; index_subquery &gt; range &gt; index &gt; ALL 其中比较重要的几个提取出来（见上图中的蓝<br>色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴<br>开发手册要求）</p>
<h4 id="6-possible-keys和key"><a href="#6-possible-keys和key" class="headerlink" title="6. possible_keys和key"></a>6. possible_keys和key</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">></span> <span class="token string">'z'</span> <span class="token operator">AND</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112017111.png" alt="image-20220327112017111" style="zoom:80%;" />

<h4 id="7、key-len-☆"><a href="#7、key-len-☆" class="headerlink" title="7、key_len ☆"></a>7、key_len ☆</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">10005</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112024603.png" alt="image-20220327112024603" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key2 <span class="token operator">=</span> <span class="token number">10126</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112031616.png" alt="image-20220327112031616" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112044277.png" alt="image-20220327112044277" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key_part1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112049754.png" alt="image-20220327112049754" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key_part1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token operator">AND</span> key_part2 <span class="token operator">=</span> <span class="token string">'b'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112058223.png" alt="image-20220327112058223" style="zoom:80%;" />

<p>练习：<br>key_len的长度计算公式：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>变长字段且允许<span class="token boolean">NULL</span> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">*</span> <span class="token punctuation">(</span> <span class="token keyword">character</span> <span class="token keyword">set</span>：
utf8<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">,</span>gbk<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">,</span>latin1<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">2</span><span class="token punctuation">(</span>变长字段<span class="token punctuation">)</span>
<span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>变长字段且不允许<span class="token boolean">NULL</span> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">*</span> <span class="token punctuation">(</span> <span class="token keyword">character</span> <span class="token keyword">set</span>：utf8<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">,</span>gbk<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">,</span>latin1<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">2</span><span class="token punctuation">(</span>变长字段<span class="token punctuation">)</span>
<span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>固定字段且允许<span class="token boolean">NULL</span> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">*</span> <span class="token punctuation">(</span> <span class="token keyword">character</span> <span class="token keyword">set</span>：utf8<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">,</span>gbk<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">,</span>latin1<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span>
<span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>固定字段且不允许<span class="token boolean">NULL</span> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">*</span> <span class="token punctuation">(</span> <span class="token keyword">character</span> <span class="token keyword">set</span>：utf8<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">,</span>gbk<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">,</span>latin1<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="8-ref"><a href="#8-ref" class="headerlink" title="8. ref"></a>8. ref</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112105377.png" alt="image-20220327112105377" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>id <span class="token operator">=</span> s2<span class="token punctuation">.</span>id<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112111014.png" alt="image-20220327112111014" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s2<span class="token punctuation">.</span>key1 <span class="token operator">=</span> UPPER<span class="token punctuation">(</span>s1<span class="token punctuation">.</span>key1<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112116033.png" alt="image-20220327112116033" style="zoom:80%;" />

<h4 id="9-rows-☆"><a href="#9-rows-☆" class="headerlink" title="9. rows ☆"></a>9. rows ☆</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">></span> <span class="token string">'z'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112122225.png" alt="image-20220327112122225" style="zoom:80%;" />

<ol start="10">
<li>filtered</li>
</ol>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">></span> <span class="token string">'z'</span> <span class="token operator">AND</span> common_field <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112127409.png" alt="image-20220327112127409" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key1 <span class="token keyword">WHERE</span>
s1<span class="token punctuation">.</span>common_field <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112132214.png" alt="image-20220327112132214" style="zoom:80%;" />

<h4 id="11-Extra-☆"><a href="#11-Extra-☆" class="headerlink" title="11. Extra ☆"></a>11. Extra ☆</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112137740.png" alt="image-20220327112137740" style="zoom:80%;" />

<ul>
<li><p>Impossible WHERE</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> <span class="token number">1</span> <span class="token operator">!=</span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112143296.png" alt="image-20220327112143296" style="zoom:80%;" />

<ul>
<li><p>Using where</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> common_field <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112148343.png" alt="image-20220327112148343" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token operator">AND</span> common_field <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112154946.png" alt="image-20220327112154946" style="zoom:80%;" />

<ul>
<li><p>No matching min/max row</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>key1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'abcdefg'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112159925.png" alt="image-20220327112159925" style="zoom:80%;" />

<ul>
<li><p>Using index</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> key1 <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112204859.png" alt="image-20220327112204859" style="zoom:80%;" />

<ul>
<li><p>Using index condition</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">></span> <span class="token string">'z'</span> <span class="token operator">AND</span> key1 <span class="token operator">LIKE</span> <span class="token string">'%a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">></span> <span class="token string">'z'</span> <span class="token operator">AND</span> key1 <span class="token operator">LIKE</span> <span class="token string">'%b'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112209772.png" alt="image-20220327112209772" style="zoom:80%;" />

<ul>
<li><p>Using join buffer (Block Nested Loop)</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>common_field <span class="token operator">=</span>
s2<span class="token punctuation">.</span>common_field<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112215113.png" alt="image-20220327112215113" style="zoom:80%;" />

<ul>
<li><p>Not exists</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key1 <span class="token keyword">WHERE</span> s2<span class="token punctuation">.</span>id <span class="token operator">IS</span>
<span class="token boolean">NULL</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112220123.png" alt="image-20220327112220123" style="zoom:80%;" />

<ul>
<li><p>Using intersect(…) 、Using union(…) 和Using sort_union(…)</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">WHERE</span> key1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token operator">OR</span> key3 <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112225335.png" alt="image-20220327112225335" style="zoom:80%;" />

<ul>
<li><p>Zero limit</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112230602.png" alt="image-20220327112230602" style="zoom:80%;" />

<ul>
<li><p>Using filesort</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> key1 <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112256035.png" alt="image-20220327112256035" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> common_field <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112302106.png" alt="image-20220327112302106" style="zoom:80%;" />

<ul>
<li><p>Using temporary</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> common_field <span class="token keyword">FROM</span> s1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112310607.png" alt="image-20220327112310607" style="zoom:80%;" />

<p>​    再比如：</p>
<pre><code><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> common_field<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> amount <span class="token keyword">FROM</span> s1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span>
common_field<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
</code></pre>
<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112315232.png" alt="image-20220327112315232" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> key1<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> amount <span class="token keyword">FROM</span> s1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> key1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112320640.png" alt="image-20220327112320640" style="zoom:80%;" />

<p>从Extra 的Using index 的提示里我们可以看出，上述查询只需要扫描idx_key1 索引就可以搞<br>定了，不再需要临时表了</p>
<ul>
<li><p>其他</p>
<p>其它特殊情况这里省略</p>
</li>
</ul>
<h4 id="12、小结"><a href="#12、小结" class="headerlink" title="12、小结"></a>12、小结</h4><ul>
<li>EXPLAIN不考虑各种Cache</li>
<li>EXPLAIN不能显示MySQL在执行查询时所作的优化工作</li>
<li>EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况</li>
<li>部分统计信息是估算的，并非精确值</li>
</ul>
<h2 id="7-EXPLAIN的进一步使用"><a href="#7-EXPLAIN的进一步使用" class="headerlink" title="7. EXPLAIN的进一步使用"></a>7. EXPLAIN的进一步使用</h2><h3 id="7-1-EXPLAIN四种输出格式"><a href="#7-1-EXPLAIN四种输出格式" class="headerlink" title="7.1 EXPLAIN四种输出格式"></a>7.1 EXPLAIN四种输出格式</h3><p>这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式： 传统格式， JSON格式， TREE格式以及可<br>视化输出。用户可以根据需要选择适用于自己的格式。</p>
<h4 id="1-传统格式"><a href="#1-传统格式" class="headerlink" title="1. 传统格式"></a>1. 传统格式</h4><p>传统格式简单明了，输出是一个表格形式，概要说明查询计划。</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> s1<span class="token punctuation">.</span>key1<span class="token punctuation">,</span> s2<span class="token punctuation">.</span>key1 <span class="token keyword">FROM</span> s1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key1 <span class="token keyword">WHERE</span>
s2<span class="token punctuation">.</span>common_field <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112331368.png" alt="image-20220327112331368" style="zoom:80%;" />

<h4 id="2-JSON格式"><a href="#2-JSON格式" class="headerlink" title="2. JSON格式"></a>2. JSON格式</h4><ul>
<li><p>JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON 。</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">EXPLAIN</span> FORMAT<span class="token operator">=</span>JSON <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></li>
</ul>
<p>我们使用# 后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON 语句的输出内容，但是大家可能<br>有疑问”cost_info” 里边的成本看着怪怪的，它们是怎么计算出来的？先看s1 表的”cost_info” 部<br>分：</p>
<pre class="line-numbers language-json" data-language="json"><code class="language-json"><span class="token property">"cost_info"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
    <span class="token property">"read_cost"</span><span class="token operator">:</span> <span class="token string">"1840.84"</span><span class="token punctuation">,</span>
    <span class="token property">"eval_cost"</span><span class="token operator">:</span> <span class="token string">"193.76"</span><span class="token punctuation">,</span>
    <span class="token property">"prefix_cost"</span><span class="token operator">:</span> <span class="token string">"2034.60"</span><span class="token punctuation">,</span>
    <span class="token property">"data_read_per_join"</span><span class="token operator">:</span> <span class="token string">"1M"</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<ul>
<li><p>read_cost 是由下边这两部分组成的：</p>
<ul>
<li>IO 成本</li>
<li>检测rows × (1 - filter) 条记录的CPU 成本</li>
</ul>
<blockquote>
<p>小贴士： rows和filter都是我们前边介绍执行计划的输出列，在JSON格式的执行计划中，rows<br>相当于rows_examined_per_scan，filtered名称不变。</p>
</blockquote>
</li>
<li><p>eval_cost 是这样计算的：</p>
<p>检测 rows × filter 条记录的成本。</p>
</li>
<li><p>prefix_cost 就是单独查询s1 表的成本，也就是：</p>
<p>read_cost + eval_cost</p>
</li>
<li><p>data_read_per_join 表示在此次查询中需要读取的数据量。</p>
</li>
</ul>
<p>对于s2 表的”cost_info” 部分是这样的：</p>
<pre class="line-numbers language-json" data-language="json"><code class="language-json"><span class="token property">"cost_info"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
    <span class="token property">"read_cost"</span><span class="token operator">:</span> <span class="token string">"968.80"</span><span class="token punctuation">,</span>
    <span class="token property">"eval_cost"</span><span class="token operator">:</span> <span class="token string">"193.76"</span><span class="token punctuation">,</span>
    <span class="token property">"prefix_cost"</span><span class="token operator">:</span> <span class="token string">"3197.16"</span><span class="token punctuation">,</span>
    <span class="token property">"data_read_per_join"</span><span class="token operator">:</span> <span class="token string">"1M"</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>由于s2 表是被驱动表，所以可能被读取多次，这里的read_cost 和eval_cost 是访问多次s2 表后累<br>加起来的值，大家主要关注里边儿的prefix_cost 的值代表的是整个连接查询预计的成本，也就是单<br>次查询s1 表和多次查询s2 表后的成本的和，也就是：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token number">968.80</span> <span class="token operator">+</span> <span class="token number">193.76</span> <span class="token operator">+</span> <span class="token number">2034.60</span> <span class="token operator">=</span> <span class="token number">3197.16</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<h4 id="3-TREE格式"><a href="#3-TREE格式" class="headerlink" title="3. TREE格式"></a>3. TREE格式</h4><p>TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描<br>述如何查询。</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> FORMAT<span class="token operator">=</span>tree <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> s1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key2 <span class="token keyword">WHERE</span>
s1<span class="token punctuation">.</span>common_field <span class="token operator">=</span> <span class="token string">'a'</span>\G
<span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span> <span class="token number">1.</span> <span class="token keyword">row</span> <span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span>
<span class="token keyword">EXPLAIN</span>: <span class="token operator">-</span><span class="token operator">></span> Nested <span class="token keyword">loop</span> <span class="token keyword">inner</span> <span class="token keyword">join</span> <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">1360.08</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">990</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span> Filter: <span class="token punctuation">(</span><span class="token punctuation">(</span>s1<span class="token punctuation">.</span>common_field <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">)</span> <span class="token operator">and</span> <span class="token punctuation">(</span>s1<span class="token punctuation">.</span>key1 <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">1013.75</span>
<span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">990</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> s1 <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">1013.75</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">9895</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span> Single<span class="token operator">-</span><span class="token keyword">row</span> <span class="token keyword">index</span> lookup <span class="token keyword">on</span> s2 <span class="token keyword">using</span> idx_key2 <span class="token punctuation">(</span>key2<span class="token operator">=</span>s1<span class="token punctuation">.</span>key1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">with</span> <span class="token keyword">index</span>
condition: <span class="token punctuation">(</span>cast<span class="token punctuation">(</span>s1<span class="token punctuation">.</span>key1 <span class="token keyword">as</span> <span class="token keyword">double</span><span class="token punctuation">)</span> <span class="token operator">=</span> cast<span class="token punctuation">(</span>s2<span class="token punctuation">.</span>key2 <span class="token keyword">as</span> <span class="token keyword">double</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.25</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span><span class="token punctuation">,</span> <span class="token number">1</span> warning <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h4 id="4-可视化输出"><a href="#4-可视化输出" class="headerlink" title="4. 可视化输出"></a>4. 可视化输出</h4><p>可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图<br>标，即可生成可视化的查询计划。<br>上图按从左到右的连接顺序显示表。红色框表示全表扫描，而绿色框表示使用索引查找。对于每个表，<br>显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表<br>的成本。</p>
<h3 id="7-2-SHOW-WARNINGS的使用"><a href="#7-2-SHOW-WARNINGS的使用" class="headerlink" title="7.2 SHOW WARNINGS的使用"></a>7.2 SHOW WARNINGS的使用</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> s1<span class="token punctuation">.</span>key1<span class="token punctuation">,</span> s2<span class="token punctuation">.</span>key1 <span class="token keyword">FROM</span> s1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>key1 <span class="token operator">=</span> s2<span class="token punctuation">.</span>key1 <span class="token keyword">WHERE</span>
s2<span class="token punctuation">.</span>common_field <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<img src="https://cdn.jsdelivr.net/gh/kuangtf/PictureBed/img/image-20220327112338725.png" alt="image-20220327112338725" style="zoom:80%;" />

<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span>\G
<span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span> <span class="token number">1.</span> <span class="token keyword">row</span> <span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span><span class="token operator">*</span>
<span class="token keyword">Level</span>: Note
Code: <span class="token number">1003</span>
Message: <span class="token comment">/* select#1 */</span> <span class="token keyword">select</span> <span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s1<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>key1<span class="token punctuation">`</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span>key1<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s2<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>key1<span class="token punctuation">`</span>
<span class="token keyword">AS</span> <span class="token punctuation">`</span>key1<span class="token punctuation">`</span> <span class="token keyword">from</span> <span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s1<span class="token punctuation">`</span> <span class="token keyword">join</span> <span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s2<span class="token punctuation">`</span> <span class="token keyword">where</span> <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s1<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>key1<span class="token punctuation">`</span> <span class="token operator">=</span>
<span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s2<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>key1<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token operator">and</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>atguigu<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>s2<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>common_field<span class="token punctuation">`</span> <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h2 id="8-分析优化器执行计划：trace"><a href="#8-分析优化器执行计划：trace" class="headerlink" title="8. 分析优化器执行计划：trace"></a>8. 分析优化器执行计划：trace</h2><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SET</span> optimizer_trace<span class="token operator">=</span><span class="token string">"enabled=on"</span><span class="token punctuation">,</span>end_markers_in_json<span class="token operator">=</span><span class="token keyword">on</span><span class="token punctuation">;</span>
<span class="token keyword">set</span> optimizer_trace_max_mem_size<span class="token operator">=</span><span class="token number">1000000</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>开启后，可分析如下语句：</p>
<ul>
<li><p>SELECT</p>
</li>
<li><p>INSERT</p>
</li>
<li><p>REPLACE</p>
</li>
<li><p>UPDATE</p>
</li>
<li><p>DELETE</p>
</li>
<li><p>EXPLAIN</p>
</li>
<li><p>SET</p>
</li>
<li><p>DECLARE</p>
</li>
<li><p>CASE</p>
</li>
<li><p>IF</p>
</li>
<li><p>RETURN</p>
</li>
<li><p>CALL</p>
</li>
</ul>
<p>测试：执行如下SQL语句</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> student <span class="token keyword">where</span> id <span class="token operator">&lt;</span> <span class="token number">10</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<p>最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 ：</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> information_schema<span class="token punctuation">.</span>optimizer_trace\G<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-json" data-language="json"><code class="language-json">*************************** <span class="token number">1</span>. row ***************************
<span class="token comment">//第1部分：查询语句</span>
QUERY<span class="token operator">:</span> select * from student where id &lt; <span class="token number">10</span>
<span class="token comment">//第2部分：QUERY字段对应语句的跟踪信息</span>
TRACE<span class="token operator">:</span> <span class="token punctuation">&#123;</span>
    <span class="token property">"steps"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
        <span class="token punctuation">&#123;</span>
            <span class="token property">"join_preparation"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//预备工作</span>
                <span class="token property">"select#"</span><span class="token operator">:</span> <span class="token number">1</span><span class="token punctuation">,</span>
                <span class="token property">"steps"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                    <span class="token punctuation">&#123;</span>
                        <span class="token property">"expanded_query"</span><span class="token operator">:</span> "<span class="token comment">/* select#1 */</span> select `student`.`id` AS
                        `id`<span class="token punctuation">,</span>`student`.`stuno` AS `stuno`<span class="token punctuation">,</span>`student`.`name` AS `name`<span class="token punctuation">,</span>`student`.`age` AS
                        `age`<span class="token punctuation">,</span>`student`.`classId` AS `classId` from `student` where (`student`.`id` &lt; <span class="token number">10</span>)"
                    <span class="token punctuation">&#125;</span>
            <span class="token punctuation">]</span> <span class="token comment">/* steps */</span>
        <span class="token punctuation">&#125;</span> <span class="token comment">/* join_preparation */</span>
        <span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
        <span class="token punctuation">&#123;</span>
        <span class="token property">"join_optimization"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//进行优化</span>
        <span class="token property">"select#"</span><span class="token operator">:</span> <span class="token number">1</span><span class="token punctuation">,</span>
        <span class="token property">"steps"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
        <span class="token punctuation">&#123;</span>
        <span class="token property">"condition_processing"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//条件处理</span>
        <span class="token property">"condition"</span><span class="token operator">:</span> <span class="token string">"WHERE"</span><span class="token punctuation">,</span>
        <span class="token property">"original_condition"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span><span class="token punctuation">,</span>
        <span class="token property">"steps"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
        <span class="token punctuation">&#123;</span>
        <span class="token property">"transformation"</span><span class="token operator">:</span> <span class="token string">"equality_propagation"</span><span class="token punctuation">,</span>
        <span class="token property">"resulting_condition"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span>
        <span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"transformation"</span><span class="token operator">:</span> <span class="token string">"constant_propagation"</span><span class="token punctuation">,</span>
    <span class="token property">"resulting_condition"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"transformation"</span><span class="token operator">:</span> <span class="token string">"trivial_condition_removal"</span><span class="token punctuation">,</span>
    <span class="token property">"resulting_condition"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span>
<span class="token punctuation">&#125;</span>
<span class="token punctuation">]</span> <span class="token comment">/* steps */</span>
<span class="token punctuation">&#125;</span> <span class="token comment">/* condition_processing */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"substitute_generated_columns"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//替换生成的列</span>
    <span class="token punctuation">&#125;</span> <span class="token comment">/* substitute_generated_columns */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"table_dependencies"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//表的依赖关系</span>
        <span class="token punctuation">&#123;</span>
            <span class="token property">"table"</span><span class="token operator">:</span> <span class="token string">"`student`"</span><span class="token punctuation">,</span>
            <span class="token property">"row_may_be_null"</span><span class="token operator">:</span> <span class="token boolean">false</span><span class="token punctuation">,</span>
            <span class="token property">"map_bit"</span><span class="token operator">:</span> <span class="token number">0</span><span class="token punctuation">,</span>
            <span class="token property">"depends_on_map_bits"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
            <span class="token punctuation">]</span> <span class="token comment">/* depends_on_map_bits */</span>
        <span class="token punctuation">&#125;</span>
    <span class="token punctuation">]</span> <span class="token comment">/* table_dependencies */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"ref_optimizer_key_uses"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//使用键</span>
    <span class="token punctuation">]</span> <span class="token comment">/* ref_optimizer_key_uses */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"rows_estimation"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//行判断</span>
        <span class="token punctuation">&#123;</span>
            <span class="token property">"table"</span><span class="token operator">:</span> <span class="token string">"`student`"</span><span class="token punctuation">,</span>
            <span class="token property">"range_analysis"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                <span class="token property">"table_scan"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                    <span class="token property">"rows"</span><span class="token operator">:</span> <span class="token number">3973767</span><span class="token punctuation">,</span>
                    <span class="token property">"cost"</span><span class="token operator">:</span> <span class="token number">408558</span>
                <span class="token punctuation">&#125;</span> <span class="token comment">/* table_scan */</span><span class="token punctuation">,</span> <span class="token comment">//扫描表</span>
                <span class="token property">"potential_range_indexes"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//潜在的范围索引</span>
                    <span class="token punctuation">&#123;</span>
                        <span class="token property">"index"</span><span class="token operator">:</span> <span class="token string">"PRIMARY"</span><span class="token punctuation">,</span>
                        <span class="token property">"usable"</span><span class="token operator">:</span> <span class="token boolean">true</span><span class="token punctuation">,</span>
                        <span class="token property">"key_parts"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                            <span class="token string">"id"</span>
                        <span class="token punctuation">]</span> <span class="token comment">/* key_parts */</span>
                    <span class="token punctuation">&#125;</span>
                <span class="token punctuation">]</span> <span class="token comment">/* potential_range_indexes */</span><span class="token punctuation">,</span>
                <span class="token property">"setup_range_conditions"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//设置范围条件</span>
                <span class="token punctuation">]</span> <span class="token comment">/* setup_range_conditions */</span><span class="token punctuation">,</span>
                <span class="token property">"group_index_range"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                    <span class="token property">"chosen"</span><span class="token operator">:</span> <span class="token boolean">false</span><span class="token punctuation">,</span>
                    <span class="token property">"cause"</span><span class="token operator">:</span> <span class="token string">"not_group_by_or_distinct"</span>
                <span class="token punctuation">&#125;</span> <span class="token comment">/* group_index_range */</span><span class="token punctuation">,</span>
                <span class="token property">"skip_scan_range"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                    <span class="token property">"potential_skip_scan_indexes"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                        <span class="token punctuation">&#123;</span>
                            <span class="token property">"index"</span><span class="token operator">:</span> <span class="token string">"PRIMARY"</span><span class="token punctuation">,</span>
                            <span class="token property">"usable"</span><span class="token operator">:</span> <span class="token boolean">false</span><span class="token punctuation">,</span>
                            <span class="token property">"cause"</span><span class="token operator">:</span> <span class="token string">"query_references_nonkey_column"</span>
                        <span class="token punctuation">&#125;</span>
                    <span class="token punctuation">]</span> <span class="token comment">/* potential_skip_scan_indexes */</span>
                <span class="token punctuation">&#125;</span> <span class="token comment">/* skip_scan_range */</span><span class="token punctuation">,</span>
                <span class="token property">"analyzing_range_alternatives"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//分析范围选项</span>
                    <span class="token property">"range_scan_alternatives"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                        <span class="token punctuation">&#123;</span>
                            <span class="token property">"index"</span><span class="token operator">:</span> <span class="token string">"PRIMARY"</span><span class="token punctuation">,</span>
                            <span class="token property">"ranges"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                                <span class="token string">"id &lt; 10"</span>
                            <span class="token punctuation">]</span> <span class="token comment">/* ranges */</span><span class="token punctuation">,</span>
                            <span class="token property">"index_dives_for_eq_ranges"</span><span class="token operator">:</span> <span class="token boolean">true</span><span class="token punctuation">,</span>
                            <span class="token property">"rowid_ordered"</span><span class="token operator">:</span> <span class="token boolean">true</span><span class="token punctuation">,</span>
                            <span class="token property">"using_mrr"</span><span class="token operator">:</span> <span class="token boolean">false</span><span class="token punctuation">,</span>
                            <span class="token property">"index_only"</span><span class="token operator">:</span> <span class="token boolean">false</span><span class="token punctuation">,</span>
                            <span class="token property">"rows"</span><span class="token operator">:</span> <span class="token number">9</span><span class="token punctuation">,</span>
                            <span class="token property">"cost"</span><span class="token operator">:</span> <span class="token number">1.91986</span><span class="token punctuation">,</span>
                            <span class="token property">"chosen"</span><span class="token operator">:</span> <span class="token boolean">true</span>
                        <span class="token punctuation">&#125;</span>
                    <span class="token punctuation">]</span> <span class="token comment">/* range_scan_alternatives */</span><span class="token punctuation">,</span>
                    <span class="token property">"analyzing_roworder_intersect"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                        <span class="token property">"usable"</span><span class="token operator">:</span> <span class="token boolean">false</span><span class="token punctuation">,</span>
                        <span class="token property">"cause"</span><span class="token operator">:</span> <span class="token string">"too_few_roworder_scans"</span>
                    <span class="token punctuation">&#125;</span> <span class="token comment">/* analyzing_roworder_intersect */</span>
                <span class="token punctuation">&#125;</span> <span class="token comment">/* analyzing_range_alternatives */</span><span class="token punctuation">,</span>
                <span class="token property">"chosen_range_access_summary"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//选择范围访问摘要</span>
                    <span class="token property">"range_access_plan"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                        <span class="token property">"type"</span><span class="token operator">:</span> <span class="token string">"range_scan"</span><span class="token punctuation">,</span>
                        <span class="token property">"index"</span><span class="token operator">:</span> <span class="token string">"PRIMARY"</span><span class="token punctuation">,</span>
                        <span class="token property">"rows"</span><span class="token operator">:</span> <span class="token number">9</span><span class="token punctuation">,</span>
                        <span class="token property">"ranges"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                            <span class="token string">"id &lt; 10"</span>
                        <span class="token punctuation">]</span> <span class="token comment">/* ranges */</span>
                    <span class="token punctuation">&#125;</span> <span class="token comment">/* range_access_plan */</span><span class="token punctuation">,</span>
                    <span class="token property">"rows_for_plan"</span><span class="token operator">:</span> <span class="token number">9</span><span class="token punctuation">,</span>
                    <span class="token property">"cost_for_plan"</span><span class="token operator">:</span> <span class="token number">1.91986</span><span class="token punctuation">,</span>
                    <span class="token property">"chosen"</span><span class="token operator">:</span> <span class="token boolean">true</span>
                <span class="token punctuation">&#125;</span> <span class="token comment">/* chosen_range_access_summary */</span>
            <span class="token punctuation">&#125;</span> <span class="token comment">/* range_analysis */</span>
        <span class="token punctuation">&#125;</span>
    <span class="token punctuation">]</span> <span class="token comment">/* rows_estimation */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"considered_execution_plans"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//考虑执行计划</span>
        <span class="token punctuation">&#123;</span>
            <span class="token property">"plan_prefix"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
            <span class="token punctuation">]</span> <span class="token comment">/* plan_prefix */</span><span class="token punctuation">,</span>
            <span class="token property">"table"</span><span class="token operator">:</span> <span class="token string">"`student`"</span><span class="token punctuation">,</span>
            <span class="token property">"best_access_path"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//最佳访问路径</span>
                <span class="token property">"considered_access_paths"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
                    <span class="token punctuation">&#123;</span>
                        <span class="token property">"rows_to_scan"</span><span class="token operator">:</span> <span class="token number">9</span><span class="token punctuation">,</span>
                        <span class="token property">"access_type"</span><span class="token operator">:</span> <span class="token string">"range"</span><span class="token punctuation">,</span>
                        <span class="token property">"range_details"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span>
                            <span class="token property">"used_index"</span><span class="token operator">:</span> <span class="token string">"PRIMARY"</span>
                        <span class="token punctuation">&#125;</span> <span class="token comment">/* range_details */</span><span class="token punctuation">,</span>
                        <span class="token property">"resulting_rows"</span><span class="token operator">:</span> <span class="token number">9</span><span class="token punctuation">,</span>
                        <span class="token property">"cost"</span><span class="token operator">:</span> <span class="token number">2.81986</span><span class="token punctuation">,</span>
                        <span class="token property">"chosen"</span><span class="token operator">:</span> <span class="token boolean">true</span>
                    <span class="token punctuation">&#125;</span>
                <span class="token punctuation">]</span> <span class="token comment">/* considered_access_paths */</span>
            <span class="token punctuation">&#125;</span> <span class="token comment">/* best_access_path */</span><span class="token punctuation">,</span>
            <span class="token property">"condition_filtering_pct"</span><span class="token operator">:</span> <span class="token number">100</span><span class="token punctuation">,</span> <span class="token comment">//行过滤百分比</span>
            <span class="token property">"rows_for_plan"</span><span class="token operator">:</span> <span class="token number">9</span><span class="token punctuation">,</span>
            <span class="token property">"cost_for_plan"</span><span class="token operator">:</span> <span class="token number">2.81986</span><span class="token punctuation">,</span>
            <span class="token number">9</span>. MySQL监控分析视图-sys schema
            <span class="token property">"chosen"</span><span class="token operator">:</span> <span class="token boolean">true</span>
        <span class="token punctuation">&#125;</span>
    <span class="token punctuation">]</span> <span class="token comment">/* considered_execution_plans */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"attaching_conditions_to_tables"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//将条件附加到表上</span>
        <span class="token property">"original_condition"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span><span class="token punctuation">,</span>
        <span class="token property">"attached_conditions_computation"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
        <span class="token punctuation">]</span> <span class="token comment">/* attached_conditions_computation */</span><span class="token punctuation">,</span>
        <span class="token property">"attached_conditions_summary"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//附加条件概要</span>
            <span class="token punctuation">&#123;</span>
                <span class="token property">"table"</span><span class="token operator">:</span> <span class="token string">"`student`"</span><span class="token punctuation">,</span>
                <span class="token property">"attached"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span>
            <span class="token punctuation">&#125;</span>
        <span class="token punctuation">]</span> <span class="token comment">/* attached_conditions_summary */</span>
    <span class="token punctuation">&#125;</span> <span class="token comment">/* attaching_conditions_to_tables */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"finalizing_table_conditions"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
        <span class="token punctuation">&#123;</span>
            <span class="token property">"table"</span><span class="token operator">:</span> <span class="token string">"`student`"</span><span class="token punctuation">,</span>
            <span class="token property">"original_table_condition"</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span><span class="token punctuation">,</span>
            <span class="token property">"final_table_condition "</span><span class="token operator">:</span> <span class="token string">"(`student`.`id` &lt; 10)"</span>
        <span class="token punctuation">&#125;</span>
    <span class="token punctuation">]</span> <span class="token comment">/* finalizing_table_conditions */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"refine_plan"</span><span class="token operator">:</span> <span class="token punctuation">[</span> <span class="token comment">//精简计划</span>
        <span class="token punctuation">&#123;</span>
            <span class="token property">"table"</span><span class="token operator">:</span> <span class="token string">"`student`"</span>
        <span class="token punctuation">&#125;</span>
    <span class="token punctuation">]</span> <span class="token comment">/* refine_plan */</span>
<span class="token punctuation">&#125;</span>
<span class="token punctuation">]</span> <span class="token comment">/* steps */</span>
<span class="token punctuation">&#125;</span> <span class="token comment">/* join_optimization */</span>
<span class="token punctuation">&#125;</span><span class="token punctuation">,</span>
<span class="token punctuation">&#123;</span>
    <span class="token property">"join_execution"</span><span class="token operator">:</span> <span class="token punctuation">&#123;</span> <span class="token comment">//执行</span>
        <span class="token property">"select#"</span><span class="token operator">:</span> <span class="token number">1</span><span class="token punctuation">,</span>
        <span class="token property">"steps"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
        <span class="token punctuation">]</span> <span class="token comment">/* steps */</span>
    <span class="token punctuation">&#125;</span> <span class="token comment">/* join_execution */</span>
<span class="token punctuation">&#125;</span>
<span class="token punctuation">]</span> <span class="token comment">/* steps */</span>
<span class="token punctuation">&#125;</span>
<span class="token comment">//第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。</span>
MISSING_BYTES_BEYOND_MAX_MEM_SIZE<span class="token operator">:</span> <span class="token number">0</span> <span class="token comment">//丢失的超出最大容量的字节</span>
<span class="token comment">//第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在</span>
调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
INSUFFICIENT_PRIVILEGES<span class="token operator">:</span> <span class="token number">0</span> <span class="token comment">//缺失权限</span>
<span class="token number">1</span> row in set (<span class="token number">0.00</span> sec)<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<h2 id="9-MySQL监控分析视图-sys-schema"><a href="#9-MySQL监控分析视图-sys-schema" class="headerlink" title="9. MySQL监控分析视图-sys schema"></a>9. MySQL监控分析视图-sys schema</h2><h3 id="9-1-Sys-schema视图摘要"><a href="#9-1-Sys-schema视图摘要" class="headerlink" title="9.1 Sys schema视图摘要"></a>9.1 Sys schema视图摘要</h3><ol>
<li><p>主机相关：以host_summary开头，主要汇总了IO延迟的信息。</p>
</li>
<li><p>Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。</p>
</li>
<li><p>I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。</p>
</li>
<li><p>内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况</p>
</li>
<li><p>连接与会话信息：processlist和session相关视图，总结了会话相关信息。</p>
</li>
<li><p>表相关：以schema_table开头的视图，展示了表的统计信息。</p>
</li>
<li><p>索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。</p>
</li>
<li><p>语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。</p>
</li>
<li><p>用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。</p>
</li>
<li><p>等待事件相关信息：以wait开头，展示等待事件的延迟情况。</p>
</li>
</ol>
<h3 id="9-2-Sys-schema视图使用场景"><a href="#9-2-Sys-schema视图使用场景" class="headerlink" title="9.2 Sys schema视图使用场景"></a>9.2 Sys schema视图使用场景</h3><p>索引情况</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#1. 查询冗余索引</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> sys<span class="token punctuation">.</span>schema_redundant_indexes<span class="token punctuation">;</span>
<span class="token comment">#2. 查询未使用过的索引</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> sys<span class="token punctuation">.</span>schema_unused_indexes<span class="token punctuation">;</span>
<span class="token comment">#3. 查询索引的使用情况</span>
<span class="token keyword">select</span> index_name<span class="token punctuation">,</span>rows_selected<span class="token punctuation">,</span>rows_inserted<span class="token punctuation">,</span>rows_updated<span class="token punctuation">,</span>rows_deleted
<span class="token keyword">from</span> sys<span class="token punctuation">.</span>schema_index_statistics <span class="token keyword">where</span> table_schema<span class="token operator">=</span><span class="token string">'dbname'</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>表相关</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment"># 1. 查询表的访问量</span>
<span class="token keyword">select</span> table_schema<span class="token punctuation">,</span>table_name<span class="token punctuation">,</span><span class="token function">sum</span><span class="token punctuation">(</span>io_read_requests<span class="token operator">+</span>io_write_requests<span class="token punctuation">)</span> <span class="token keyword">as</span> io <span class="token keyword">from</span>
sys<span class="token punctuation">.</span>schema_table_statistics <span class="token keyword">group</span> <span class="token keyword">by</span> table_schema<span class="token punctuation">,</span>table_name <span class="token keyword">order</span> <span class="token keyword">by</span> io <span class="token keyword">desc</span><span class="token punctuation">;</span>
<span class="token comment"># 2. 查询占用bufferpool较多的表</span>
<span class="token keyword">select</span> object_schema<span class="token punctuation">,</span>object_name<span class="token punctuation">,</span>allocated<span class="token punctuation">,</span><span class="token keyword">data</span>
<span class="token keyword">from</span> sys<span class="token punctuation">.</span>innodb_buffer_stats_by_table <span class="token keyword">order</span> <span class="token keyword">by</span> allocated <span class="token keyword">limit</span> <span class="token number">10</span><span class="token punctuation">;</span>
<span class="token comment"># 3. 查看表的全表扫描情况</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> sys<span class="token punctuation">.</span>statements_with_full_table_scans <span class="token keyword">where</span> db<span class="token operator">=</span><span class="token string">'dbname'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>语句相关</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#1. 监控SQL执行的频率</span>
<span class="token keyword">select</span> db<span class="token punctuation">,</span>exec_count<span class="token punctuation">,</span>query <span class="token keyword">from</span> sys<span class="token punctuation">.</span>statement_analysis
<span class="token keyword">order</span> <span class="token keyword">by</span> exec_count <span class="token keyword">desc</span><span class="token punctuation">;</span>
<span class="token comment">#2. 监控使用了排序的SQL</span>
<span class="token keyword">select</span> db<span class="token punctuation">,</span>exec_count<span class="token punctuation">,</span>first_seen<span class="token punctuation">,</span>last_seen<span class="token punctuation">,</span>query
<span class="token keyword">from</span> sys<span class="token punctuation">.</span>statements_with_sorting <span class="token keyword">limit</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token comment">#3. 监控使用了临时表或者磁盘临时表的SQL</span>
<span class="token keyword">select</span> db<span class="token punctuation">,</span>exec_count<span class="token punctuation">,</span>tmp_tables<span class="token punctuation">,</span>tmp_disk_tables<span class="token punctuation">,</span>query
<span class="token keyword">from</span> sys<span class="token punctuation">.</span>statement_analysis <span class="token keyword">where</span> tmp_tables<span class="token operator">></span><span class="token number">0</span> <span class="token operator">or</span> tmp_disk_tables <span class="token operator">></span><span class="token number">0</span>
<span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token punctuation">(</span>tmp_tables<span class="token operator">+</span>tmp_disk_tables<span class="token punctuation">)</span> <span class="token keyword">desc</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>

<p>IO相关</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#1. 查看消耗磁盘IO的文件</span>
<span class="token keyword">select</span> <span class="token keyword">file</span><span class="token punctuation">,</span>avg_read<span class="token punctuation">,</span>avg_write<span class="token punctuation">,</span>avg_read<span class="token operator">+</span>avg_write <span class="token keyword">as</span> avg_io
<span class="token keyword">from</span> sys<span class="token punctuation">.</span>io_global_by_file_by_bytes <span class="token keyword">order</span> <span class="token keyword">by</span> avg_read <span class="token keyword">limit</span> <span class="token number">10</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<p>Innodb 相关</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment">#1. 行锁阻塞情况</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> sys<span class="token punctuation">.</span>innodb_lock_waits<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>


                
            </div>
            <hr/>

            

    <div class="reprint" id="reprint-statement">
        
            <div class="reprint__author">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-user">
                        文章作者:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="/about" rel="external nofollow noreferrer">Skyu</a>
                </span>
            </div>
            <div class="reprint__type">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-link">
                        文章链接:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="https://kuangty.gitee.io/2021/10/31/MySQL/explain/">https://kuangty.gitee.io/2021/10/31/MySQL/explain/</a>
                </span>
            </div>
            <div class="reprint__notice">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-copyright">
                        版权声明:
                    </i>
                </span>
                <span class="reprint-info">
                    本博客所有文章除特別声明外，均采用
                    <a href="https://creativecommons.org/licenses/by/4.0/deed.zh" rel="external nofollow noreferrer" target="_blank">CC BY 4.0</a>
                    许可协议。转载请注明来源
                    <a href="/about" target="_blank">Skyu</a>
                    !
                </span>
            </div>
        
    </div>

    <script async defer>
      document.addEventListener("copy", function (e) {
        let toastHTML = '<span>复制成功，请遵循本文的转载规则</span><button class="btn-flat toast-action" onclick="navToReprintStatement()" style="font-size: smaller">查看</a>';
        M.toast({html: toastHTML})
      });

      function navToReprintStatement() {
        $("html, body").animate({scrollTop: $("#reprint-statement").offset().top - 80}, 800);
      }
    </script>



            <div class="tag_share" style="display: block;">
                <div class="post-meta__tag-list" style="display: inline-block;">
                    
                        <div class="article-tag">
                            
                                <a href="/tags/explain/">
                                    <span class="chip bg-color">explain</span>
                                </a>
                            
                        </div>
                    
                </div>
                <div class="post_share" style="zoom: 80%; width: fit-content; display: inline-block; float: right; margin: -0.15rem 0;">
                    <link rel="stylesheet" type="text/css" href="/libs/share/css/share.min.css">
<div id="article-share">

    
    <div class="social-share" data-sites="twitter,facebook,google,qq,qzone,wechat,weibo,douban,linkedin" data-wechat-qrcode-helper="<p>微信扫一扫即可分享！</p>"></div>
    <script src="/libs/share/js/social-share.min.js"></script>
    

    

</div>

                </div>
            </div>
            
                <style>
    #reward {
        margin: 40px 0;
        text-align: center;
    }

    #reward .reward-link {
        font-size: 1.4rem;
        line-height: 38px;
    }

    #reward .btn-floating:hover {
        box-shadow: 0 6px 12px rgba(0, 0, 0, 0.2), 0 5px 15px rgba(0, 0, 0, 0.2);
    }

    #rewardModal {
        width: 320px;
        height: 350px;
    }

    #rewardModal .reward-title {
        margin: 15px auto;
        padding-bottom: 5px;
    }

    #rewardModal .modal-content {
        padding: 10px;
    }

    #rewardModal .close {
        position: absolute;
        right: 15px;
        top: 15px;
        color: rgba(0, 0, 0, 0.5);
        font-size: 1.3rem;
        line-height: 20px;
        cursor: pointer;
    }

    #rewardModal .close:hover {
        color: #ef5350;
        transform: scale(1.3);
        -moz-transform:scale(1.3);
        -webkit-transform:scale(1.3);
        -o-transform:scale(1.3);
    }

    #rewardModal .reward-tabs {
        margin: 0 auto;
        width: 210px;
    }

    .reward-tabs .tabs {
        height: 38px;
        margin: 10px auto;
        padding-left: 0;
    }

    .reward-content ul {
        padding-left: 0 !important;
    }

    .reward-tabs .tabs .tab {
        height: 38px;
        line-height: 38px;
    }

    .reward-tabs .tab a {
        color: #fff;
        background-color: #ccc;
    }

    .reward-tabs .tab a:hover {
        background-color: #ccc;
        color: #fff;
    }

    .reward-tabs .wechat-tab .active {
        color: #fff !important;
        background-color: #22AB38 !important;
    }

    .reward-tabs .alipay-tab .active {
        color: #fff !important;
        background-color: #019FE8 !important;
    }

    .reward-tabs .reward-img {
        width: 210px;
        height: 210px;
    }
</style>

<div id="reward">
    <a href="#rewardModal" class="reward-link modal-trigger btn-floating btn-medium waves-effect waves-light red">赏</a>

    <!-- Modal Structure -->
    <div id="rewardModal" class="modal">
        <div class="modal-content">
            <a class="close modal-close"><i class="fas fa-times"></i></a>
            <h4 class="reward-title">你的赏识是我前进的动力</h4>
            <div class="reward-content">
                <div class="reward-tabs">
                    <ul class="tabs row">
                        <li class="tab col s6 alipay-tab waves-effect waves-light"><a href="#alipay">支付宝</a></li>
                        <li class="tab col s6 wechat-tab waves-effect waves-light"><a href="#wechat">微 信</a></li>
                    </ul>
                    <div id="alipay">
                        <img src="/medias/reward/alipay.jpg" class="reward-img" alt="支付宝打赏二维码">
                    </div>
                    <div id="wechat">
                        <img src="/medias/reward/wechat.png" class="reward-img" alt="微信打赏二维码">
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
    $(function () {
        $('.tabs').tabs();
    });
</script>

            
        </div>
    </div>

    

    

    

    

    

    

    

    

    

<article id="prenext-posts" class="prev-next articles">
    <div class="row article-row">
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge left-badge text-color">
                <i class="fas fa-chevron-left"></i>&nbsp;上一篇</div>
            <div class="card">
                <a href="/2021/10/31/MySQL/MySQL%E5%B8%B8%E7%94%A8%E8%AF%AD%E6%B3%95/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/1.jpg" class="responsive-img" alt="MySQL 常见语法">
                        
                        <span class="card-title">MySQL 常见语法</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            
                        
                    </div>
                    <div class="publish-info">
                        <span class="publish-date">
                            <i class="far fa-clock fa-fw icon-date"></i>2021-10-31
                        </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/MySQL/" class="post-category">
                                    MySQL
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/MySQL-%E5%9F%BA%E6%9C%AC%E8%AF%AD%E6%B3%95/">
                        <span class="chip bg-color">MySQL 基本语法</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge right-badge text-color">
                下一篇&nbsp;<i class="fas fa-chevron-right"></i>
            </div>
            <div class="card">
                <a href="/2021/10/31/MySQL/MySQL%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%E4%B8%8E%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/14.jpg" class="responsive-img" alt="MySQL 索引优化与查询优化">
                        
                        <span class="card-title">MySQL 索引优化与查询优化</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            
                        
                    </div>
                    <div class="publish-info">
                            <span class="publish-date">
                                <i class="far fa-clock fa-fw icon-date"></i>2021-10-31
                            </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/MySQL/" class="post-category">
                                    MySQL
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/MySQL-%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%E4%B8%8E%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96/">
                        <span class="chip bg-color">MySQL 索引优化与查询优化</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
    </div>
</article>

</div>



<!-- 代码块功能依赖 -->
<script type="text/javascript" src="/libs/codeBlock/codeBlockFuction.js"></script>

<!-- 代码语言 -->

<script type="text/javascript" src="/libs/codeBlock/codeLang.js"></script>


<!-- 代码块复制 -->

<script type="text/javascript" src="/libs/codeBlock/codeCopy.js"></script>


<!-- 代码块收缩 -->

<script type="text/javascript" src="/libs/codeBlock/codeShrink.js"></script>


    </div>
    <div id="toc-aside" class="expanded col l3 hide-on-med-and-down">
        <div class="toc-widget card" style="background-color: white;">
            <div class="toc-title"><i class="far fa-list-alt"></i>&nbsp;&nbsp;目录</div>
            <div id="toc-content"></div>
        </div>
    </div>
</div>

<!-- TOC 悬浮按钮. -->

<div id="floating-toc-btn" class="hide-on-med-and-down">
    <a class="btn-floating btn-large bg-color">
        <i class="fas fa-list-ul"></i>
    </a>
</div>


<script src="/libs/tocbot/tocbot.min.js"></script>
<script>
    $(function () {
        tocbot.init({
            tocSelector: '#toc-content',
            contentSelector: '#articleContent',
            headingsOffset: -($(window).height() * 0.4 - 45),
            collapseDepth: Number('0'),
            headingSelector: 'h1, h2, h3, h4'
        });

        // modify the toc link href to support Chinese.
        let i = 0;
        let tocHeading = 'toc-heading-';
        $('#toc-content a').each(function () {
            $(this).attr('href', '#' + tocHeading + (++i));
        });

        // modify the heading title id to support Chinese.
        i = 0;
        $('#articleContent').children('h1, h2, h3, h4').each(function () {
            $(this).attr('id', tocHeading + (++i));
        });

        // Set scroll toc fixed.
        let tocHeight = parseInt($(window).height() * 0.4 - 64);
        let $tocWidget = $('.toc-widget');
        $(window).scroll(function () {
            let scroll = $(window).scrollTop();
            /* add post toc fixed. */
            if (scroll > tocHeight) {
                $tocWidget.addClass('toc-fixed');
            } else {
                $tocWidget.removeClass('toc-fixed');
            }
        });

        
        /* 修复文章卡片 div 的宽度. */
        let fixPostCardWidth = function (srcId, targetId) {
            let srcDiv = $('#' + srcId);
            if (srcDiv.length === 0) {
                return;
            }

            let w = srcDiv.width();
            if (w >= 450) {
                w = w + 21;
            } else if (w >= 350 && w < 450) {
                w = w + 18;
            } else if (w >= 300 && w < 350) {
                w = w + 16;
            } else {
                w = w + 14;
            }
            $('#' + targetId).width(w);
        };

        // 切换TOC目录展开收缩的相关操作.
        const expandedClass = 'expanded';
        let $tocAside = $('#toc-aside');
        let $mainContent = $('#main-content');
        $('#floating-toc-btn .btn-floating').click(function () {
            if ($tocAside.hasClass(expandedClass)) {
                $tocAside.removeClass(expandedClass).hide();
                $mainContent.removeClass('l9');
            } else {
                $tocAside.addClass(expandedClass).show();
                $mainContent.addClass('l9');
            }
            fixPostCardWidth('artDetail', 'prenext-posts');
        });
        
    });
</script>

    

</main>




    <footer class="page-footer bg-color">
    
        <link rel="stylesheet" href="/libs/aplayer/APlayer.min.css">
<style>
    .aplayer .aplayer-lrc p {
        
        display: none;
        
        font-size: 12px;
        font-weight: 700;
        line-height: 16px !important;
    }

    .aplayer .aplayer-lrc p.aplayer-lrc-current {
        
        display: none;
        
        font-size: 15px;
        color: #42b983;
    }

    
    .aplayer.aplayer-fixed.aplayer-narrow .aplayer-body {
        left: -66px !important;
    }

    .aplayer.aplayer-fixed.aplayer-narrow .aplayer-body:hover {
        left: 0px !important;
    }

    
</style>
<div class="">
    
    <div class="row">
        <meting-js class="col l8 offset-l2 m10 offset-m1 s12"
                   server="netease"
                   type="playlist"
                   id="503838841"
                   fixed='true'
                   autoplay='false'
                   theme='#42b983'
                   loop='all'
                   order='random'
                   preload='auto'
                   volume='0.7'
                   list-folded='true'
        >
        </meting-js>
    </div>
</div>

<script src="/libs/aplayer/APlayer.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/meting@2/dist/Meting.min.js"></script>

    

    <div class="container row center-align"
         style="margin-bottom: 15px !important;">
        <div class="col s12 m8 l8 copy-right">
            Copyright&nbsp;&copy;
            
                <span id="year">2019-2022</span>
            
            <span id="year">2019</span>
            <a href="/about" target="_blank">Gtwff</a>
            |&nbsp;Powered by&nbsp;<a href="https://hexo.io/" target="_blank">Hexo</a>
            |&nbsp;Theme&nbsp;<a href="https://github.com/blinkfox/hexo-theme-matery" target="_blank">Matery</a>
            <br>
            
                &nbsp;<i class="fas fa-chart-area"></i>&nbsp;站点总字数:&nbsp;<span
                        class="white-color">577.4k</span>
            
            
            
                
            
            
                <span id="busuanzi_container_site_pv">
                &nbsp;|&nbsp;<i class="far fa-eye"></i>&nbsp;总访问量:&nbsp;
                    <span id="busuanzi_value_site_pv" class="white-color"></span>
            </span>
            
            
                <span id="busuanzi_container_site_uv">
                &nbsp;|&nbsp;<i class="fas fa-users"></i>&nbsp;总访问人数:&nbsp;
                    <span id="busuanzi_value_site_uv" class="white-color"></span>
            </span>
            
            <br>

            <!-- 运行天数提醒. -->
            
                <span id="sitetime"> Loading ...</span>
                <script>
                    var calcSiteTime = function () {
                        var seconds = 1000;
                        var minutes = seconds * 60;
                        var hours = minutes * 60;
                        var days = hours * 24;
                        var years = days * 365;
                        var today = new Date();
                        var startYear = "2019";
                        var startMonth = "6";
                        var startDate = "28";
                        var startHour = "0";
                        var startMinute = "0";
                        var startSecond = "0";
                        var todayYear = today.getFullYear();
                        var todayMonth = today.getMonth() + 1;
                        var todayDate = today.getDate();
                        var todayHour = today.getHours();
                        var todayMinute = today.getMinutes();
                        var todaySecond = today.getSeconds();
                        var t1 = Date.UTC(startYear, startMonth, startDate, startHour, startMinute, startSecond);
                        var t2 = Date.UTC(todayYear, todayMonth, todayDate, todayHour, todayMinute, todaySecond);
                        var diff = t2 - t1;
                        var diffYears = Math.floor(diff / years);
                        var diffDays = Math.floor((diff / days) - diffYears * 365);

                        // 区分是否有年份.
                        var language = 'zh-CN';
                        if (startYear === String(todayYear)) {
                            document.getElementById("year").innerHTML = todayYear;
                            var daysTip = 'This site has been running for ' + diffDays + ' days';
                            if (language === 'zh-CN') {
                                daysTip = '本站已运行 ' + diffDays + ' 天';
                            } else if (language === 'zh-HK') {
                                daysTip = '本站已運行 ' + diffDays + ' 天';
                            }
                            document.getElementById("sitetime").innerHTML = daysTip;
                        } else {
                            document.getElementById("year").innerHTML = startYear + " - " + todayYear;
                            var yearsAndDaysTip = 'This site has been running for ' + diffYears + ' years and '
                                + diffDays + ' days';
                            if (language === 'zh-CN') {
                                yearsAndDaysTip = '本站已运行 ' + diffYears + ' 年 ' + diffDays + ' 天';
                            } else if (language === 'zh-HK') {
                                yearsAndDaysTip = '本站已運行 ' + diffYears + ' 年 ' + diffDays + ' 天';
                            }
                            document.getElementById("sitetime").innerHTML = yearsAndDaysTip;
                        }
                    }

                    calcSiteTime();
                </script>
            
            <br>
            
        </div>
        <div class="col s12 m4 l4 social-link social-statis">
    <a href="https://github.com/kuangtianyu" class="tooltipped" target="_blank" data-tooltip="访问我的GitHub" data-position="top" data-delay="50">
        <i class="fab fa-github"></i>
    </a>



    <a href="mailto:1575235124@qq.com" class="tooltipped" target="_blank" data-tooltip="邮件联系我" data-position="top" data-delay="50">
        <i class="fas fa-envelope-open"></i>
    </a>







    <a href="tencent://AddContact/?fromId=50&fromSubId=1&subcmd=all&uin=1575235124" class="tooltipped" target="_blank" data-tooltip="QQ联系我: 1575235124" data-position="top" data-delay="50">
        <i class="fab fa-qq"></i>
    </a>





    <a href="https://www.zhihu.com/people/kuang-tian-yu-59" class="tooltipped" target="_blank" data-tooltip="关注我的知乎: https://www.zhihu.com/people/kuang-tian-yu-59" data-position="top" data-delay="50">
        <i class="fab fa-zhihu1">知</i>
    </a>



    <a href="/atom.xml" class="tooltipped" target="_blank" data-tooltip="RSS 订阅" data-position="top" data-delay="50">
        <i class="fas fa-rss"></i>
    </a>

</div>
    </div>
</footer>

<div class="progress-bar"></div>


    <!-- 搜索遮罩框 -->
<div id="searchModal" class="modal">
    <div class="modal-content">
        <div class="search-header">
            <span class="title"><i class="fas fa-search"></i>&nbsp;&nbsp;搜索</span>
            <input type="search" id="searchInput" name="s" placeholder="请输入搜索的关键字"
                   class="search-input">
        </div>
        <div id="searchResult"></div>
    </div>
</div>

<script type="text/javascript">
$(function () {
    var searchFunc = function (path, search_id, content_id) {
        'use strict';
        $.ajax({
            url: path,
            dataType: "xml",
            success: function (xmlResponse) {
                // get the contents from search data
                var datas = $("entry", xmlResponse).map(function () {
                    return {
                        title: $("title", this).text(),
                        content: $("content", this).text(),
                        url: $("url", this).text()
                    };
                }).get();
                var $input = document.getElementById(search_id);
                var $resultContent = document.getElementById(content_id);
                $input.addEventListener('input', function () {
                    var str = '<ul class=\"search-result-list\">';
                    var keywords = this.value.trim().toLowerCase().split(/[\s\-]+/);
                    $resultContent.innerHTML = "";
                    if (this.value.trim().length <= 0) {
                        return;
                    }
                    // perform local searching
                    datas.forEach(function (data) {
                        var isMatch = true;
                        var data_title = data.title.trim().toLowerCase();
                        var data_content = data.content.trim().replace(/<[^>]+>/g, "").toLowerCase();
                        var data_url = data.url;
                        data_url = data_url.indexOf('/') === 0 ? data.url : '/' + data_url;
                        var index_title = -1;
                        var index_content = -1;
                        var first_occur = -1;
                        // only match artiles with not empty titles and contents
                        if (data_title !== '' && data_content !== '') {
                            keywords.forEach(function (keyword, i) {
                                index_title = data_title.indexOf(keyword);
                                index_content = data_content.indexOf(keyword);
                                if (index_title < 0 && index_content < 0) {
                                    isMatch = false;
                                } else {
                                    if (index_content < 0) {
                                        index_content = 0;
                                    }
                                    if (i === 0) {
                                        first_occur = index_content;
                                    }
                                }
                            });
                        }
                        // show search results
                        if (isMatch) {
                            str += "<li><a href='" + data_url + "' class='search-result-title'>" + data_title + "</a>";
                            var content = data.content.trim().replace(/<[^>]+>/g, "");
                            if (first_occur >= 0) {
                                // cut out 100 characters
                                var start = first_occur - 20;
                                var end = first_occur + 80;
                                if (start < 0) {
                                    start = 0;
                                }
                                if (start === 0) {
                                    end = 100;
                                }
                                if (end > content.length) {
                                    end = content.length;
                                }
                                var match_content = content.substr(start, end);
                                // highlight all keywords
                                keywords.forEach(function (keyword) {
                                    var regS = new RegExp(keyword, "gi");
                                    match_content = match_content.replace(regS, "<em class=\"search-keyword\">" + keyword + "</em>");
                                });

                                str += "<p class=\"search-result\">" + match_content + "...</p>"
                            }
                            str += "</li>";
                        }
                    });
                    str += "</ul>";
                    $resultContent.innerHTML = str;
                });
            }
        });
    };

    searchFunc('/search.xml', 'searchInput', 'searchResult');
});
</script>

    <!-- 回到顶部按钮 -->
<div id="backTop" class="top-scroll">
    <a class="btn-floating btn-large waves-effect waves-light" href="#!">
        <i class="fas fa-arrow-up"></i>
    </a>
</div>


    <script src="/libs/materialize/materialize.min.js"></script>
    <script src="/libs/masonry/masonry.pkgd.min.js"></script>
    <script src="/libs/aos/aos.js"></script>
    <script src="/libs/scrollprogress/scrollProgress.min.js"></script>
    <script src="/libs/lightGallery/js/lightgallery-all.min.js"></script>
    <script src="/js/matery.js"></script>

    <!-- Baidu Analytics -->

    <!-- Baidu Push -->

<script>
    (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
            bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
            bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
    })();
</script>

    
    <script src="/libs/others/clicklove.js" async="async"></script>
    
    
    <script async src="/libs/others/busuanzi.pure.mini.js"></script>
    

    

    

    <!--腾讯兔小巢-->
    
    
    <script type="text/javascript" color="0,0,255"
        pointColor="0,0,255" opacity='0.7'
        zIndex="-1" count="99"
        src="/libs/background/canvas-nest.js"></script>
    

    
    
    <script type="text/javascript" size="150" alpha='0.6'
        zIndex="-1" src="/libs/background/ribbon-refresh.min.js" async="async"></script>
    

    
    <script type="text/javascript" src="/libs/background/ribbon-dynamic.js" async="async"></script>
    

    
    <script src="/libs/instantpage/instantpage.js" type="module"></script>
    

</body>

</html>
